Lookup aggregation within nested array

Working with Data
1

I have a data structure where I have a deeply nested array of objects, which then contain an array of ids. I want to do a lookup of those ids against another table, and substitute the full objects in place in this aggregation.

My attempted pipeline looks like this:

db.orders.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$lookup": {
      "from": "inventory",
      "localField": "items.things",
      "foreignField": "sku",
      "as": "items.things"
    }
  }
])

The problem is that this only matches against the first object in the items array, and it overwrites other fields within the items scope, leaving just the things property, with the lookup of the first array.

Setup can be found here: Mongo playground

Sample output:

[
  {
    "_id": 1,
    "items": {
      "things": [
        {
          "_id": 1,
          "description": "product 1",
          "instock": 120,
          "sku": "almonds"
        },
        {
          "_id": 3,
          "description": "product 3",
          "instock": 60,
          "sku": "cashews"
        }
      ]
    },
    "price": 12,
    "quantity": 2
  }
]

With the following sample input (from orders)

"orders": [
    {
      "_id": 1,
      "items": [
        {
          "name": "first",
          "things": [
            "almonds"
          ]
        },
        {
          "name": "second",
          "things": [
            "cashews",
            "almonds"
          ]
        }
      ],
      "price": 12,
      "quantity": 2
    },
    {
      "_id": 2,
      "item": [
        "pecans",
        "cashews"
      ],
      "price": 20,
      "quantity": 1
    },
    {
      "_id": 3
    }
  ],
3

Hi - thank you for this response, and maybe I wasn’t clear. Both of these solutions seem to match what I could get with a simple join. What I am looking for is a solution where items is transformed from:

"items": [
        {
          "name": "first",
          "things": [
            "almonds"
          ]
        },
        {
          "name": "second",
          "things": [
            "cashews",
            "almonds"
          ]
        }
      ]

To:

"items": [
        {
          "name": "first",
          "things": [
            {
              "_id": 1,
              "sku": "almonds",
              "description": "product 1",
              "instock": 120
           }
          ]
        },
        {
          "name": "second",
          "things": [
           {
             "_id": 3,
             "sku": "cashews",
             "description": "product 3",
             "instock": 60
           },
           {
            "_id": 1,
            "sku": "almonds",
            "description": "product 1",
            "instock": 120
           }
          ]
        }
      ]
1 Like
4

lookup can’t update results in a nested array. you need to do separate processes to join them in nested,

  • $lookup stage, set result in a separate property “things”
  • $addFields stage,
  • $map to iterate loop of items array
  • $filter to iterate loop of things array that is lookup result and find the matching items by checking $in operator condition
  • $mergeObjects to merge the current object of the item and updated things properly
  • $$REMOVE to remove the things property that is not needed
db.orders.aggregate([
  { "$match": { "_id": 1 } },
  {
    "$lookup": {
      "from": "inventory",
      "localField": "items.things",
      "foreignField": "sku",
      "as": "things"
    }
  },
  {
    $addFields: {
      items: {
        $map: {
          input: "$items",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              {
                things: {
                  $filter: {
                    input: "$things",
                    cond: { $in: ["$$this.sku", "$$item.things"] }
                  }
                }
              }
            ]
          }
        }
      },
      things: "$$REMOVE"
    }
  }
])
```
2 Likes
5

Thanks - I’m going to have to study this a bit to see exactly how this works… but it does work!

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.