Aggregation and projection help

Working with Data
,
1

Hi, I am new to MongoDB aggregation. I am trying to solve one issue relating multiple collections where I have to generate a collaborative view of user orders.

Please see Mongo playground which contains the dataset I am using for the problem.

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 am looking for an output as below:

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

Hi Neeraj,

If you need the exact structure which you mentioned there are several ways to achieve this by using combinations of ($group and $unwind) or ($addFields and $mergeObjects). I am sharing a solution which uses $addFields and $mergeObjects - Mongo playground

Hope this solves your problem.

1 Like
3

Thanks @Akshat_Gupta3 for the quick reply and solution, this is exactly what I was looking for.

1 Like
4

Hi @Akshat_Gupta3, one quick query I have around calculating the total price per item for orders. Let’s say per product price is stored in products collection, how do I go about creating a new field (total) in output orders array which will be product of qty(stored in orders collection) and price(stored in products collection) for individual products?

5

For now, I have worked it out like this Mongo playground. Is there a different optimized way of achieving it?

6

Yes,
You do not need to run the whole mapping again, instead you can do this - Mongo playground

1 Like
7

Thanks once again @Akshat_Gupta3 , can you point me to a good aggregation learning resource if you know of any?

8

Mongo University is typically good:

Also:

2 Likes
9

Thank you @John_Sewell

10

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.