CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworklookup
webdog
webdog

Reputation: 120

Aggregation pipeline to lookup and merge nested documents

I am struggling with writing an aggregation pipeline to lookup nested documents by their _id and return a specific name without overwriting the existing keys/values in the data. I have managed to do this for the nested array, but am unable to do it for an array that is nested within the nested array.

So I want to lookup the _id of each ingredient and each subIngredient and merge them with the data for these ingredients that already exists (i.e. qty, measure).

Here is what I have so far: https://mongoplayground.net/p/ft4oIMm_8wg

Products Collection:

[
    {
      "_id": {
        "$oid": "5ecf269bceb735416db0b329"
      },
      "id": 36,
      "title": "Product 1",
      "description": {
        "generalInformation": "Some information",
        "activeIngredients": [
          {
            "_id": 1636,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1626,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          {
            "_id": 1234,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1122,
                "qty": 16.6,
                "measure": "µg"
              },
              {
                "_id": 1212,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          
        ]
      },
      
    },
    {
      "_id": {
        "$oid": "5ecf269bceb735416db0b346"
      },
      "id": 36,
      "title": "Product 2",
      "description": {
        "generalInformation": "Some information",
        "activeIngredients": [
          {
            "_id": 1234,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1122,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          {
            "_id": 1234,
            "qty": 133.5,
            "measure": "µg",
            "subIngredient": [
              {
                "_id": 1122,
                "qty": 16.6,
                "measure": "µg"
              },
              {
                "_id": 1212,
                "qty": 16.6,
                "measure": "µg"
              }
            ],
            
          },
          
        ]
      },
      
    }
  ]

Ingredients Collection:

[
    {
      "_id": 1234,
      "name": "Ingredient 1",
      
    },
    {
      "_id": 1122,
      "name": "Ingredient 2",
      
    },
    {
      "_id": 1212,
      "name": "Ingredient 3",
      
    },
    {
      "_id": 1636,
      "name": "Ingredient 4",
      
    },
    {
      "_id": 1626,
      "name": "Ingredient 5",
      
    }
  ]

What should be returned:

[
  {
    "_id": ObjectId("5ecf269bceb735416db0b329"),
    "title": "Product 1"
    "description": {
      "activeIngredients": {
        "_id": 1636,
        "measure": "µg",
        "name": "Ingredient 4",
        "qty": 133.5,
        "subIngredient": [
          {
            "_id": 1626,
            "measure": "µg",
            "qty": 16.6
          }
        ]
      },
      "generalInformation": "Some information"
    },
    "ingredients": [
      {
        "_id": 1636,
        "measure": "µg",
        "name": "Ingredient 4",
        "qty": 133.5,
        "subIngredient": [
          {
            "_id": 1626,
            "measure": "µg",
            "qty": 16.6,
            "name": "Ingredient 2"
          }
        ]
      },
      {
        "_id": 1234,
        "measure": "µg",
        "name": "Ingredient 1",
        "qty": 133.5,
        "subIngredient": [
          {
            "_id": 1122,
            "measure": "µg",
            "qty": 16.6,
            "name": "Ingredient 2"
          },
          {
            "_id": 1212,
            "measure": "µg",
            "qty": 16.6,
            "name": "Ingredient 2"
          }
        ]
      }
    ]
    
  },
  
]

My current pipeline:

[
  {
    "$unwind": {
      "path": "$description.activeIngredients",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "description.activeIngredients._id",
      "foreignField": "_id",
      "as": "description.activeIngredients.name"
    }
  },
  {
    "$addFields": {
      "description.activeIngredients.name": {
        "$arrayElemAt": [
          "$description.activeIngredients.name.name",
          0
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "ingredients": {
        "$push": "$description.activeIngredients"
      },
      "description": {
        "$first": "$description"
      },
      "title": {
        "$first": "$title"
      }
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "ingredients.subIngredient._id",
      "foreignField": "_id",
      "as": "subIngredients"
    }
  }
]

Appreciate any help. Thanks.

Upvotes: 1

Views: 650

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22276

You're not far off and you can achieve this result in multiple different ways, one of which is to just $unwind the subingredients array, $lookup on that and finally adding another $group stage to restructure the document.

As you've clearly shown you know how to do all these things i'll show a different way that utilizes operators like $map, $indexOfArray and Mongo's v3.6 $lookup syntax.

The strategy is instead of unwinding the subarray we just $lookup all the relevant sub-ingredients and then "merge" the two arrays using the operators i specified.

i.e taking:

[ {id: 5, name: "name"} ];
[ {id: 5, qty: 25} ]

And making them into:

[ {id: 5, name: "name", qty: 25} ]

Here's how we do it:

db.products.aggregate([
  {
    "$unwind": {
      "path": "$description.activeIngredients",
      "preserveNullAndEmptyArrays": false
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "localField": "description.activeIngredients._id",
      "foreignField": "_id",
      "as": "description.activeIngredients.name"
    }
  },
  {
    "$addFields": {
      "description.activeIngredients.name": {
        "$arrayElemAt": [
          "$description.activeIngredients.name.name",
          0
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "ingredients",
      "let": {
        sub: "$description.activeIngredients.subIngredient"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              "$setIsSubset": [
                [
                  "$_id"
                ],
                {
                  $map: {
                    input: "$$sub",
                    as: "datum",
                    in: "$$datum._id"
                  }
                }
              ]
            }
          }
        }
      ],
      "as": "subIngredients"
    }
  },
  {
    "$addFields": {
      "description.activeIngredients.subIngredient": {
        $map: {
          input: "$description.activeIngredients.subIngredient",
          as: "sub",
          in: {
            "$mergeObjects": [
              "$$sub",
              {
                name: {
                  $arrayElemAt: [
                    "$subIngredients.name",
                    {
                      "$indexOfArray": [
                        "$subIngredients._id",
                        "$$sub._id"
                      ]
                    }
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "ingredients": {
        "$push": "$description.activeIngredients"
      },
      "description": {
        "$first": "$description"
      },
      "title": {
        "$first": "$title"
      }
    }
  }
])

MongoPlayground

Upvotes: 1

Related Questions