Is it possible to $lookup without $unwind to use a property of current matched item?

I was trying to help in another post here : Lookup & populate objects in array. I have a working solution, but I wonder if we could do that with a single $lookup operation.

The array items we use to $lookup are objects themselves. It would not be a problem if we were just replacing the whole item, but we need to keep the original item (or parts of it at least) and insert the result back into it.

// user
{
  "_id":1,
  "cart":[
    {"_id":2,"type":"A"},
    {"_id":3,"type":"B"}
  ]
}
// items
[
  {"_id":2,"name":"item 1"},
  {"_id":3,"name":"item 2"}
]

// expected
{
  "_id":1,
  "cart":[
    {"_id":2,"type":"A",item:{"_id":2,"name":"item 1"}},
    {"_id":3,"type":"B",item:{"_id":3,"name":"item 2"}}
  ]
}

I used “let” and “pipeline” and wrote the following query but the “type” was not what I expected:

{
  from: "items",
  foreignField: "_id",
  localField: "cart._id",
  let:{type:"$cart.type"},
  pipeline:[
    {$project:
      {
        _id:1,
        type:"$$type",
        item:"$$ROOT"
      }
    }
  ],
  as: "result"
}

here is the problem: instead of taking the value of the current item, “let” scans the whole array and extracts the “type” from all items, and sends this array to the pipeline: type:["A","B"].

Is there a simpler way than $unwind/$group, or without complex $match if that matters, that I missed?

Hello @Yilmaz_Durmaz,

Here don’t need lookup with the pipeline, you can pass an array of ids in localFields property,
Yes, You can use another approach without $unwind and $group stages,

  • $map to iterate loop of cart array
  • $filter to iterate loop of items array and find the specific _id's item
  • $first to get the first element as an object from the above-filtered result, the alternate option is $arrayElemAt if you are using lower version of the MongoDB
  • $mergeObjects to merge the current cart object with the above-filtered item property, the alternate option is you can specify the properties name instead of using this operator
  • You can remove the items array by using $$REMOVE command because it’s not needed in the result, if you are using $project stage then don’t need this operation.
db.user.aggregate([
  {
    "$lookup": {
      "from": "items",
      "localField": "cart._id",
      "foreignField": "_id",
      "as": "items"
    }
  },
  {
    "$addFields": {
      "cart": {
        "$map": {
          "input": "$cart",
          "in": {
            "$mergeObjects": [
              "$$this",
              {
                "item": {
                  "$first": {
                    "$filter": {
                      "input": "$items",
                      "as": "i",
                      "cond": { "$eq": ["$$i._id", "$$this._id"] }
                    }
                  }
                }
              }
            ]
          }
        }
      },
      "items": "$$REMOVE"
    }
  }
])

Playground

3 Likes

Thanks, @turivishal, for giving your time

I am a bit lost in the nesting levels, so I will need time to digest it :wink: