Using aggregation to expand object in nested object without overriding other values

I have two collections, one with orders and one with items

Orders:

{
     _id: "123456",
     name: "John Doe",
     items: [
         {
            item: "7890", 
            count: 4
         },
         {
            item: "6543", 
            count: 4
         },
     ]
}

Items:

{
     _id: "7890",
     name: "item1"
},
{
     _id: "6543",
     name: "item2"
}

Ideally, I would like whenever an order is pulled up, the items will populate with the count requested; as shown below

{
     _id: "123456",
     name: "John Doe",
     items: [
         {
            item: "7890", 
            name: "item1",
            count: 4
         },
         {
            item: "6543", 
            name: "item2",
            count: 4
         },
     ]
}

Currently, I have tried the following lookup in the Orders collection;

{
    "$lookup": {
      "from": "items",
      "localField": "items.item",
      "foreignField": "_id",
      "as": "items"
    }
 }

However, that lookup completely overrides the ‘count’ field in the order and gives me this;

{
     _id: "123456",
     name: "John Doe",
     items: [
         {
            item: "7890", 
            name: "item1"
         },
         {
            item: "6543", 
            name: "item2"
         },
     ]
}

Is there a way to expand the ‘item’ object in the ‘order.items’ array without it overriding the ‘count’ field?, I would think it could be something done with $mergeObjects, but I am not experienced enough with the aggregation pipeline to understand how it works,

Playground

Thank you for the advice ahead of time.

Hello @Travis_Engle, Welcome to the MongoDB community forum,

To avoid every lookup and the whole process, you can store the item’s name along with item id and count` properties.

The rule of thumb whenever you design your schema is:

Data that is accessed together should be stored together.

If you still want to understand query how to solve your problem then here you go,

  • Store lookup results in items_lookup property because it will replace the value in an existing property
  • $mep to iterate loop of the items array, we are going to merge the name property from the items_lookup
  • $indexOfArray to get the array index of _id/id/item property, this will help us to find the name from the same index in the next step
  • $arrayElemAt to get the name property’s value by giving the array index, that we found from the above step
  • $mergeObjects to merge the current object by $$i of items array and the name property that we found from the above steps
db.orders.aggregate([
  {
    "$lookup": {
      "from": "items",
      "localField": "items.item",
      "foreignField": "_id",
      "as": "items_lookup"
    }
  },
  {
    "$project": {
      "name": 1,
      "items": {
        "$map": {
          "input": "$items",
          "as": "i",
          "in": {
            "$mergeObjects": [
              "$$i",
              {
                "name": {
                  "$arrayElemAt": [
                    "$items_lookup.name",
                    { "$indexOfArray": ["$items_lookup._id", "$$i.item"] }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

There are many ways in aggregation query to achieve this result!

Thank you for the response, and the explanation of the process!

One question I do have is would this same pipeline work if there was more fields in the ‘items’ db instead of just the name, such as a ‘sku’ and ‘manufacturer’? Or would it be wiser computation-wise for as orders are created to just store all the item information within the order since you mentioned that;

I would think it would be easier to have the reference to pull the data so the information isn’t duplicated within the database, but I can also understand how it would be easier since as the number of available items can increase that it would increase the time needed to run such function.

Thanks again!

Hello @Travis_Engle,

No, it requires a different approach, something like this Playground

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