CODEWITHSUNDEEP
mongodbaggregation-framework
Nimrod
Nimrod

Reputation: 422

perform lookup on array from another collection in MongoDB

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.

Upvotes: 2

Views: 1355

Answers (2)

mickl
mickl

Reputation: 49985

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

Upvotes: 1

Ashh
Ashh

Reputation: 46491

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

Upvotes: 1

Related Questions