I am having a collection which contains the data like the following and want to have the desirable output which I have mentioned below.

db={
  collectionA: [
    {
      "id": ObjectId("63b7c24c06ebe7a8fd11777b"),
      "uniqueRefId": "UUID-2023-0001",
      "products": [
        {
          "productIndex": 1,
          "productCategory": ObjectId("63b7c24c06ebe7a8fd11777b"),
          "productOwners": [
            ObjectId("63b7c2fd06ebe7a8fd117781")
          ]
        },
        {
          "productIndex": 2,
          "productCategory": ObjectId("63b7c24c06ebe7a8fd11777b"),
          "productOwners": [
            ObjectId("63b7c2fd06ebe7a8fd117781"),
            ObjectId("63b7c12706ebe7a8fd117778")
          ]
        },
        {
          "productIndex": 3,
          "productCategory": "",
          "productOwners": ""
        }
      ]
    }
  ],
  collectionB: [
    {
      "_id": ObjectId("63b7c2fd06ebe7a8fd117781"),
      "fullname": "Jim Corbett",
      "email": "[email protected]"
    },
    {
      "_id": ObjectId("63b7c12706ebe7a8fd117778"),
      "fullname": "Carry Minatti",
      "email": "[email protected]"
    },
    
  ]
}

Desirable Output = [
    {
        "id": ObjectId("507f1f77bcf86cd799439011"),
        "uniqueRefId": "UUID-2023-0001",
        "products": [
            {
                "productIndex": 1,
                "productCategory": ObjectId('614g2f77bff86cd755439021'),
                "productOwners": [
                    {
                        "_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
                        "fullname": "Jim Corbett",
                        "email": "[email protected]"
                    }
                ]
            },
            {
                "productIndex": 2,
                "productCategory": ObjectId('614g2f77bff86cd755439021'),
                "productOwners": [
                    {
                        "_id": ObjectId("63ac1e59c0afb8b6f2d41acd"),
                        "fullname": "Jim Corbett",
                        "email": "[email protected]"
                    },
                    {
                        "_id": ObjectId("63ac1e59c0afb8b6f2d41ace"),
                        "fullname": "Carry Minatti",
                        "email": "[email protected]"
                    }
                ]
            },
            {
                "productIndex": 3,
                "productCategory": "",
                "productOwners": ""
            }
        ]
    }
]

In the collectionA we are having other documents as well, its not just one document. Similarly for collectionB we are having other documents too.

How we can get this desirable output?

I am expecting the mongodb query for getting this solution.

I have implemented the lookup like the following

db.collectionA.aggregate([
  {
    "$lookup": {
      "from": "collectionB",
      "localField": "products.productOwners",
      "foreignField": "_id",
      "as": "inventory_docs"
    }
  }
])

CodePudding user response：

You can try this:

db.collectionA.aggregate([
  {
    "$unwind": "$products"
  },
  {
    "$lookup": {
      "from": "collectionB",
      "localField": "products.productOwners",
      "foreignField": "_id",
      "as": "products.productOwners"
    }
  },
  {
    "$group": {
      "_id": {
        id: "$id",
        uniqueRefId: "$uniqueRefId"
      },
      "products": {
        "$push": "$products"
      }
    }
  },
  {
    "$project": {
      id: "$_id.id",
      uniqueRefId: "$_id.uniqueRefId",
      products: 1,
      _id: 0
    }
  }
])

Playground link.

In this query, we do the following:

1. First we unwind the products array, using $unwind.
2. Then we calculate productOwners, using $lookup.
3. Then we group the unwinded elements, using $group.
4. Finally we, project the desired output using $project.