I have an items collection:

[
   {_id: '1', name: 'A', description: 'Some description 1'}, 
   {_id: '2', name: 'B', description: 'Some description 2'}, 
   {_id: '3', name: 'C', description: 'Some description 3'}, 
   {_id: '4', name: 'D', description: 'Some description 4'}, 
]

I have another collection cItems:

[
    {_id: '1', itemId: '1', items:[ {itemId: '2', qty: 4}, {itemId: '3', qty: 5}  ]},
    {_id: '2', itemId: '4', items:[ {itemId: '2', qty: 1}, {itemId: '3', qty: 1}  ]},
]

I am trying to get the details of the item based on itemId in CItems collection using aggregate.

cItems.aggregate([
      { $match: { _id: { $eq: categoryId } } },
      { $unwind: "$items" },
      {
        $lookup: {
          from: "items",
          let: {
            itemId: "$items.itemId",
            items: "$items",
          },
          pipeline: [
            { $match: { $expr: { $eq: ["$_id", "$$itemId"] } } },
            {
              $replaceRoot: {
                newRoot: { $mergeObjects: ["$$items", "$$ROOT"] },
              },
            },
            {
              $project: {
                qty: 1,
                name: 1,
                description: 1,
              },
            },
          ],
          as: "items",
        },
      },
      {
        $group: {
          _id: "$_id",
          items: { $push: { $first: "$items" } },
          name: { $first: "$name" },
          description: { $first: "$description" },
        },
      },
    ]);

Results I am getting is:

{
    "_id": "1",
    "items": [
    {
      "qty": 4,
      "_id": "2",
      "name": "A",
      "description": "Some description 2"
    },
    {
      "qty": 5,
      "_id": "3",
      "name": "C",
      "description": "Some description 3"
    }
  ],
    "name": null,
    "description": null
}

The problem is it is showing null for name & description for itemId that is not in the array.

Expected output:

{
    "_id": "1",
    "items": [
    {
      "qty": 4,
      "_id": "2",
      "name": "A",
      "description": "Some description 2"
    },
    {
      "qty": 5,
      "_id": "3",
      "name": "C",
      "description": "Some description 3"
    }
  ],
    "name": 'A',
    "description": "Some description 1"
}

I am using the latest version of MongoDB.

CodePudding user response：

You miss out on the $lookup stage to join with items for the parent item (itemId) [2nd stage].

And to get the parent item's name & description, you need $arrayElemAt to get the first item of parentItem [last stage].

Solution 1

db.cItems.aggregate([
  {
    $match: {
      _id: {
        $eq: categoryId
      }
    }
  },
  {
    $lookup: {
      from: "items",
      localField: "itemId",
      foreignField: "_id",
      as: "parentItem"
    }
  },
  {
    "$unwind": "$items"
  },
  {
    $lookup: {
      from: "items",
      let: {
        itemId: "$items.itemId",
        items: "$items"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$itemId"
              ]
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: {
              $mergeObjects: [
                "$$items",
                "$$ROOT"
              ]
            }
          }
        },
        {
          $project: {
            qty: 1,
            name: 1,
            description: 1
          }
        }
      ],
      as: "items"
    }
  },
  {
    $group: {
      _id: "$_id",
      items: {
        $push: {
          $first: "$items"
        }
      },
      name: {
        $first: {
          "$arrayElemAt": [
            "$parentItem.name",
            0
          ]
        }
      },
      description: {
        $first: {
          "$arrayElemAt": [
            "$parentItem.description",
            0
          ]
        }
      }
    }
  }
])

Sample Mongo Playground (Solution 1)

Solution 2

db.cItems.aggregate([
  {
    $match: {
      _id: {
        $eq: categoryId
      }
    }
  },
  {
    $lookup: {
      from: "items",
      localField: "itemId",
      foreignField: "_id",
      as: "parentItem"
    }
  },
  {
    $lookup: {
      from: "items",
      let: {
        items: "$items"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$items.itemId"
              ]
            }
          }
        },
        {
          "$replaceRoot": {
            "newRoot": {
              "$mergeObjects": [
                {
                  $arrayElemAt: [
                    "$$items",
                    0
                  ]
                },
                "$$ROOT"
              ]
            }
          }
        }
      ],
      as: "items"
    }
  },
  {
    $project: {
      _id: 1,
      itemId: 1,
      items: 1,
      name: {
        $arrayElemAt: [
          "$parentItem.name",
          0
        ]
      },
      description: {
        $arrayElemAt: [
          "$parentItem.description",
          0
        ]
      }
    }
  }
])

Sample Mongo Playground (Solution 2)