Question 174976 in Mongodb Question posted in
The official documentation can be found here.

How can I rewrite this?

db.orders.aggregate([
  {
    "$lookup": {
      "from": "inventory",
      "as": "result",
      "let": {"_id": "$_id"},
      "pipeline": [ 
         { $match: 
           { $expr: 
             { 
               $and: [ { $eq: ["$adder_id", some_object_id] }, { $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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    db.orders.aggregate([
   {
      "$lookup": {
         "from": "inventory",
         "as": "result",
         "let": { "id": "$_id" },
         "pipeline": [
            { $match: { adder_id: some_object_id } },
            { $set: { keep: { $cond: [ { $eq: ["$friend_id", "$$id"] }, true, false ] } } },
            { $match: { $expr: { $eq: ["$keep", true] } } }
         ]
      }
   }
])

    Based on manual testing, this actually appears to have the desired effect. The query time is now significantly faster.

    But of course, I don't think the second $match is using the index. Or will it. Can someone confirm?


  2. 0

    I would use it like this:

    db.orders.aggregate([
   {
      "$lookup": {
         "from": "inventory",
         "as": "result",
         "let": { "_id": "$_id" },
         "pipeline": [
            { $match: { adder_id: some_object_id } },
            { $match: { $expr: { $eq: ["$friend_id", "$$_id"] } } }
         ]
      }
   }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search