Aggregation Help Needed

[Neeraj_Sathe]

I am trying to solve a problem surrounding the following dataset and looking for assistance from community members.

Input Dataset

db={
  orders: [
    {
      _id: ObjectId("65fee31937b6938d0107afe9"),
      products: [
        {
          _id: ObjectId("65fee0f137b6938d0107afe3"),
          qty: 20
        },
        {
          _id: ObjectId("65fee0f137b6938d0107afe4"),
          qty: 2
        }
      ],
      uid: ObjectId("65fee0b337b6938d0107afe1")
    },
    {
      _id: ObjectId("65fee31937b6938d0107afea"),
      products: [
        {
          _id: ObjectId("65fee0f137b6938d0107afe2"),
          qty: 4
        },
        {
          _id: ObjectId("65fee0f137b6938d0107afe4"),
          qty: 1
        }
      ],
      uid: ObjectId("65fee0b337b6938d0107afe0")
    }
  ],
  users: [
    {
      _id: ObjectId("65fee0b337b6938d0107afe0"),
      name: "User 1",
      address: "Address 1"
    },
    {
      _id: ObjectId("65fee0b337b6938d0107afe1"),
      name: "User 2",
      address: "Address 2"
    }
  ],
  products: [
    {
      _id: ObjectId("65fee0f137b6938d0107afe2"),
      name: "Product 1"
    },
    {
      _id: ObjectId("65fee0f137b6938d0107afe3"),
      name: "Product 2"
    },
    {
      _id: ObjectId("65fee0f137b6938d0107afe4"),
      name: "Product 3"
    }
  ]
}

I wanted to perform aggregation on orders collection, here is the Mongo Playground link containing the dataset and initial aggregation.

Desired Output

[
  {
    "_id": ObjectId("65fee31937b6938d0107afe9"),
    "products": [
      {
        "_id": ObjectId("65fee0f137b6938d0107afe3"),
        "name": "Product 2",
        "price": 10,
        "qty": 20,
        "total": 200
      },
      {
        "_id": ObjectId("65fee0f137b6938d0107afe4"),
        "name": "Product 3",
        "price": 40,
        "qty": 2,
        "total": 80
      }
    ],
    "user": {
      "_id": ObjectId("65fee0b337b6938d0107afe1"),
      "address": "Address 2",
      "name": "User 2"
    }
  },
  {
    "_id": ObjectId("65fee31937b6938d0107afea"),
    "products": [
      {
        "_id": ObjectId("65fee0f137b6938d0107afe2"),
        "name": "Product 1",
        "price": 30,
        "qty": 4,
        "total": 120
      },
      {
        "_id": ObjectId("65fee0f137b6938d0107afe4"),
        "name": "Product 3",
        "price": 40,
        "qty": 1,
        "total": 40
      }
    ],
    "user": {
      "_id": ObjectId("65fee0b337b6938d0107afe0"),
      "address": "Address 1",
      "name": "User 1"
    }
  }
]

@Akshat_Gupta3 can you help?

[Akshat_Gupta3 (Akshat Gupta)]

Hi Neeraj,

Here is a possible pipeline that can solve your use case - Mongo playground

[Neeraj_Sathe]

Thank you @Akshat_Gupta3 once again for helping out.