MongoDB $lookup - conditional value for localfield?-CodePudding

I have 2 collections I want to combine using lookup.

Collection1: _AddressSyncStatus fields I wanna use from this collection: "address"

Collection2: EthTokenTransfers Fields I want to use from this collection: "to_address", "from_address".

Now when I use mongo compass, the lookup filter expects a local field, in my case the local field of EthTokenTransfers to join the collections. My problem now is, that I want to lookup where address from _AddressSyncStatus is either EthTokenTransfers.from_address OR EthTokenTransfers.to_address.

Is this possible using aggregations?

{
from: '_AddressSyncStatus',
localField: 'string', //from_address OR to_address
foreignField: 'address',
as: 'synced_address'
}

CodePudding user response：

One way to do it is using the $lookup pipeline with $or:

db.EthTokenTransfers.aggregate([
  {
    $lookup: {
      from: "_AddressSyncStatus",
      let: {
        from_address: "$from_address",
        to_address: "$to_address"
      },
      pipeline: [
        {
          $match: {
            $expr: {$or: [{$eq: ["$_id", "$$from_address"]},
                {$eq: ["$_id", "$$to_address"]}
              ]
            }
          }
        }
      ],
      as: "synced_address"
    }
  }
])

As you can see here.

But I think that for the rset of the work with this data, it will be more convenient like this:

db.EthTokenTransfers.aggregate([
  {
    $lookup: {
      from: "_AddressSyncStatus",
      localField: "from_address",
      foreignField: "_id",
      as: "synced_address_from"
    }
  },
  {
    $lookup: {
      from: "_AddressSyncStatus",
      localField: "to_address",
      foreignField: "_id",
      as: "synced_address_to"
    }
  }
])

As you can see here