So let's say I have a table of transaction data that is shaped like so:

{
  tokenAddress: string; // Address of token
  to: string; // Address of wallet receiving token
  from: string; // Address of wallet sending token
  quantity: number; // Number of tokens sent
}

I'd like to perform an aggregation that transforms this data like so

{
  tokenAddress: string; // Address of token
  walletAddress: string; // Each wallet has a row
  quantity: number; // Number of tokens in wallet
}

I am doing this currently by pulling the flat transaction data out and performing a pretty complex reduce in the application code.

export const getAddressesTokensTransferred = async (
  walletAddresses: string[]
) => {
  const collection = await getCollection('tokenTransfers');

  const result = await collection
    .find({
      $or: [
        { from: { $in: walletAddresses } },
        { to: { $in: walletAddresses } },
      ],
    })
    .toArray();

  return result.reduce((acc, { tokenAddress, quantity, to, from }) => {
    const useTo = walletAddresses.includes(to);
    const useFrom = walletAddresses.includes(from);
    let existingFound = false;
    for (const existing of acc) {
      if (existing.tokenAddress === tokenAddress) {
        if (useTo && existing.walletAddress === to) {
          existingFound = true;
          existing.quantity  = quantity;
          break;
        } else if (useFrom && existing.walletAddress === from) {
          existingFound = true;
          existing.quantity -= quantity;
          break;
        }
      }
    }

    if (!existingFound) {
      if (useTo) {
        acc.push({ tokenAddress, walletAddress: to, quantity });
      }
      if (useFrom) {
        acc.push({
          tokenAddress,
          walletAddress: from,
          quantity: quantity * -1,
        });
      }
    }

    return acc;
  }, [] as { tokenAddress: string; walletAddress: string; quantity: number }[]);
};

I feel like there MUST be a better way to do this within MongoDB, but I'm just not experienced enough with it to know how. Any help is greatly appreciated!

Edit - Adding some sample documents:

Input walletAddresses:

[
  '0x72caf7c477ccab3f95913b9d8cdf35a1caf25555', 
  '0x5b6e57baeb62c530cf369853e15ed25d0c82a866'
]

Result from initial find:

[
  {
    to: "0x123457baeb62c530cf369853e15ed25d0c82a866",
    from: "0x4321f7c477ccab3f95913b9d8cdf35a1caf25555",
    quantity: 5,
    tokenAddress: "0x12129ec85eebe10a9b01af64e89f9d76d22cea18",
  },
  {
    to: "0x123457baeb62c530cf369853e15ed25d0c82a866",
    from: "0x0000000000000000000000000000000000000000",
    quantity: 5,
    tokenAddress: "0x12129ec85eebe10a9b01af64e89f9d76d22cea18"
  },
  {
    to: "0x4321f7c477ccab3f95913b9d8cdf35a1caf25555",
    from: "0x0000000000000000000000000000000000000000",
    quantity: 5,
    tokenAddress: "0x12129ec85eebe10a9b01af64e89f9d76d22cea18"
  },
  {
    to: "0x4321f7c477ccab3f95913b9d8cdf35a1caf25555",
    from: "0x0000000000000000000000000000000000000000",
    quantity: 5,
    tokenAddress: "0x12129ec85eebe10a9b01af64e89f9d76d22cea18"
  }
]

This is a small sample with just two wallets (the other 0x000, and any others not in the walletAddresses array can be discarded essentially), and a single token (there would be many, we would want a row for each of them that have a transaction with the wallets)

The desired result would be

[
  {
    tokenAddress: '0x86ba9ec85eebe10a9b01af64e89f9d76d22cea18',
    walletAddress: '0x72caf7c477ccab3f95913b9d8cdf35a1caf25555',
    quantity: 5
  },
  {
    tokenAddress: '0x86ba9ec85eebe10a9b01af64e89f9d76d22cea18',
    walletAddress: '0x5b6e57baeb62c530cf369853e15ed25d0c82a866',
    quantity: 10
  }
]

CodePudding user response：

One option is to "duplicate" the transactions and keep them temporarily per walletAddress. This way we can group them by walletAddress:

db.collection.aggregate([
  {
    $project: {
      data: [
        {walletAddress: "$from",
          quantity: {$multiply: ["$quantity", -1]},
          tokenAddress: "$tokenAddress"},
        {walletAddress: "$to",
          quantity: "$quantity",
          tokenAddress: "$tokenAddress"}
      ]
    }
  },
  {$unwind: "$data"},
  {$group: {
      _id: "$data.walletAddress",
      quantity: {$sum: "$data.quantity"},
      tokenAddress: {$first: "$data.tokenAddress"}
  }},
  {$match: {
      _id: {$in: [
          "0x4321f7c477ccab3f95913b9d8cdf35a1caf25555",
          "0x123457baeb62c530cf369853e15ed25d0c82a866"
        ]
      }
  }}
])

See how it works on the playground example