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

[Travis_Engle]

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.

[turivishal (Vishal Turi)]

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!

[Travis_Engle]

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;

@turivishal

Data that is accessed together should be stored together.

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!

[turivishal (Vishal Turi)]

Hello @Travis_Engle,

@Travis_Engle

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’?

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