Partial static and dynamic $lookup

Working with Data
, ,
1 
db.orders.aggregate([
  {
    "$lookup": {
      "from": "inventory",
      "as": "result",
      "let": {"_id": "$_id"},,
      "pipeline": [ 
         { $match: 
           { $expr: 
             { 
               $and: [ { $eq: ["$_id", ObjectId()] }, { $eq:  [ "$friend_id",  "$$_id" ] } ] 
             }  
           } 
        }
      ]
    }
  }
])

In this $lookup, there are two $match conditions. The first is static, while the second is dynamic.

Would mongodb’s query planner be able to optimize and run the static match condition once to find the superset of matching documents, cached it, and then from this superset run the second match condition for each document? And, bonus, is it able to use the index for the second conditon?

And if mongodb currently isn’t smart enough to do the above, how can I make request for this feature to be added?

3

Hi @Big_Cat_Public_Safety_Act and welcome to MongoDB community forums!!

If I understand your question correctly, you are trying to match the _id with a specific ObjectID and then trying to perform a lookup with the friend_id as the foreign field.

Based on my understanding, I created two collections as follows:

Atlas atlas-b8d6l3-shard-0 [primary] test> db.id1.find()
[
  { _id: ObjectId("6474851a9cf9ae4249964107") },
  { _id: ObjectId("647487b49cf9ae4249964109") },
  { _id: ObjectId("647487b99cf9ae424996410a") }
]
Atlas atlas-b8d6l3-shard-0 [primary] test> db.id2.find()
[
  {
    _id: ObjectId("6474879a9cf9ae4249964108"),
    friend_id: ObjectId("6474851a9cf9ae4249964107")
  }
]

and I tried to execute the query as mentioned which did not returned any result.
I tried to rewrite the query as shown below:

Atlas atlas-b8d6l3-shard-0 [primary] test> db.id1.aggregate([
...     {
...         '$match': {
...             '_id': ObjectId('6474851a9cf9ae4249964107')
...         }
...     }, {
...         '$lookup': {
...             'from': 'id2',
...             'localField': '_id',
...             'foreignField': 'friend_id',
...             'as': 'result'
...         }
...     }
... ])

and it returned the result as:

[
  {
    _id: ObjectId("6474851a9cf9ae4249964107"),
    result: [
      {
        _id: ObjectId("6474879a9cf9ae4249964108"),
        friend_id: ObjectId("6474851a9cf9ae4249964107")
      }
    ]
  }
]

If this is not something you are looking for, could you help me with a sample document from the collections along with the desired output for the aggregation performed. Also, specify the MongoDB version you are using.

Regards
Aasawari