perform lookup on array from another collection in MongoDB

Question

I have a collection of Orders. each order has a list of Items, and each Item has catalog_id, which is an ObjectId pointing to the Catalogs collection. I need an aggregate query that will retrieve certain orders - each order with its Items in extended fashion including the Catalog name and SKU. i.e:

Original data structure:

Orders: [{
 _id : ObjectId('ord1'),
items : [{
   catalog_id: ObjectId('xyz1'),
   qty: 5
},
{
   catalog_id: ObjectId('xyz2'),
   qty: 3
}]
Catalogs: [{
 _id : ObjectId('xyz1') 
name: 'my catalog name',
SKU: 'XxYxZx1'
},{
 _id : ObjectId('xyz2') 
name: 'my other catalog name',
SKU: 'XxYxZx2'
}
]

ideal outcome would be:

Orders: [{
 _id : ObjectId('ord1'),
items : [{
   catalog_id: ObjectId('xyz1'),
   catalog_name: 'my catalog name',
   catalog_SKU: 'XxYxZx1' , 
   qty: 5
},
{
   catalog_id: ObjectId('xyz2'),
   catalog_name: 'my other catalog name',
   catalog_SKU: 'XxYxZx2' , 
   qty: 3
}
]

What I did so far was:

db.orders.aggregate(
    [
    {
        $match: {merchant_order_id: 'NIM333'}
    },
    {
        $lookup: {
          from: "catalogs",
          //localField: 'items.catalog_id',
          //foreignField: '_id',
          let: { 'catalogId' : 'items.catalog_id' },
          pipeline: [
              {
                $match : {$expr:{$eq:["$catalogs._id", "$$catalogId"]}}
              },
              {
                  $project: {"name": 1, "merchant_SKU": 1 }
              }
          ],
          as: "items_ex"
        },
    },
])

but items_ex comes out empty for some reason i cannot understand.

the localField and foreignField option does work, but retrieves the entire Catalog object, and I would like to project it, so I have to pipeline. — Nimrod, Jan 06 '20 at 12:46

mickl · Answer 1 · 2020-01-06T14:26:23.097

1

You're missing a dollar sign when you define your pipeline variable. There should be:

let: { 'catalogId' : '$items.catalog_id' },

and also this expression returns an array to you need $in instead of $eq:

{
    $lookup: {
    from: "catalogs",
    let: { 'catalogId' : 'items.catalog_id' },
    pipeline: [
        {
            $match : {$expr:{$in:["$_id", "$$catalogId"]}}
        },
        {
            $project: {"name": 1, "merchant_SKU": 1 }
        }
    ],
    as: "items_ex"
    }
}

Mongo Playground

edited Jan 06 '20 at 14:26

answered Jan 06 '20 at 13:00

mickl

48,568
9
60
89

I did your two proposed corrections, but it still returns an empty items_ex array – Nimrod Jan 06 '20 at 14:22
@Nimrod please try lowercase `catalogs` as collection name – mickl Jan 06 '20 at 14:26
@Nimrod could you compare your code with Mongo Playground ? – mickl Jan 06 '20 at 14:36
Works perfectly on the playground, doesn't work on my server. could it be because i'm on 4.01, and playground is 4.2.2? Additionally, how do I project the quantity as well? I tried to project it and got an array of all the quantities... – Nimrod Jan 06 '20 at 15:51
Try to check if all the fields names are the same. I didn't change much from your example (just converted `_id` to be strings in this example) – mickl Jan 06 '20 at 15:59
Ok, I hope I'll manage, but the problem with quantity returning an array instead of the record's value remains... – Nimrod Jan 06 '20 at 16:03
@Nimrod try this way: https://mongoplayground.net/p/tc-19O4U8yt – mickl Jan 06 '20 at 18:33

Ashh · Answer 2 · 2020-01-06T13:07:12.733

1

You need to first $unwind the items and reconstruct the array back using $group to match the exact position of qty with the catalogs_id inside the items array

db.orders.aggregate([
  { "$match": { "merchant_order_id": "NIM333" }},
  { "$unwind": "$items" },
  { "$lookup": {
    "from": "catalogs",
    "let": { "catalogId": "$items.catalog_id", "qty": "$items.qty" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$catalogId"] } }},
      { "$project": { "name": 1, "merchant_SKU": 1, "qty": "$$qty" }}
    ],
    "as": "items"
  }},
  { "$unwind": "$items" },
  { "$group": {
    "_id": "$_id",
    "items": { "$push": "$items" },
    "data": { "$first": "$$ROOT" }
  }},
  { "$replaceRoot": {
    "newRoot": {
      "$mergeObjects": ["$data", { "items": "$items" }]
    }
  }}
])

MongoPlayground

edited Jan 06 '20 at 13:07

answered Jan 06 '20 at 13:02

Ashh

44,693
14
105
132

very nice, thanks. I wanted to avoid unwinding, but it looks solid. – Nimrod Jan 06 '20 at 14:21
One more way https://stackoverflow.com/questions/54617074/mongodb-lookup-when-foreign-field-is-an-array-of-objects – Ashh Jan 06 '20 at 14:36
The problem with this is that it becomes an inner join, which means that every order without items disappears... this is why unwind/group is not the way to go i think – Nimrod Jan 06 '20 at 14:56
What do you mean by *order without items disappears*? You can always maintain the order when using `$unwind` and `$group` – Ashh Jan 07 '20 at 05:02
Yes, you are right, I saw the option to keep nulls in $unwind. thanks. – Nimrod Jan 07 '20 at 11:03

perform lookup on array from another collection in MongoDB

2 Answers2