MongoDB nested array group by Multiple values

Question

I have a nested array that I want to group according to branch, department, and product. For each of these products, I want to get the sum of the quantity.

This is my array:

    const array = [{
      "branch": "Main Office",
      "department": "Accounting",
      "transaction": [{
        "referenceCode": "REF1",
        "product": [{
          "product": "pen",
          "quantity": 5, "color": "blue", "size": 10
        },
        {
          "product": "marker",
          "quantity": 7, "color": "black", "size": 15
        },
        {
          "product": "paper",
          "quantity": 27, "color": "gree", "size": 14
        },
        ]
      },

      {
        "referenceCode": "REF2",
        "product": [{
          "product": "pen",
          "quantity": 3, "color": "brown", "size": 9
        },
        {
          "product": "marker",
          "quantity": 6, "color": "pink", "size": 6
        },
        {
          "product": "paper",
          "quantity": 22, "color": "indigo", "size": 5
        },
        ]
      },
    ],
    },

    {
      "branch": "Sub-office",
      "department": "Warehouse",
      "transaction": [{
        "referenceCode": "REF3",
        "product": [{
          "product": "pen",
          "quantity": 30, "color": "blue", "size": 5
        },
        {
          "product": "marker",
          "quantity": 30, "color": "gold", "size": 34
        },
        ]
      },

      {
        "referenceCode": "REF4",
        "product": [{
          "product": "pen",
          "quantity": 3, "color": "silver", "size": 2
        },
        {
          "product": "marker",
          "quantity": 6, , "color": "white", "size": 3
        },
        {
          "product": "paper",
          "quantity": 30, , "color": "violet", "size": 5
        },
        ]
      },
    ],
    },
  ]

This is my desired result:

[
    {  "branch": "Main Office",  "department": "Accounting", "product": "pen",  "quantity": 8 },
    {  "branch": "Main Office",  "department": "Accounting", "product": "marker",  "quantity": 13 },
    {  "branch": "Main Office",  "department": "Accounting", "product": "paper",  "quantity": 49 },
    {  "branch": "Sub-office",  "department": "Warehouse", "product": "pen",  "quantity": 33 },
    {  "branch": "Sub-office",  "department": "Warehouse", "product": "marker",  "quantity": 36 },
    {  "branch": "Sub-office",  "department": "Warehouse", "product": "paper",  "quantity": 30 }
  ]

I believe the aggregation framework best solves this but I don't know. If there are solutions other than the aggregation framework, please let me know.

Valijon · Accepted Answer · 2020-07-05 08:22:00Z

2

You need to perform the MongoDB aggregation pipeline with $group stage.

Explanation

We flatten transaction and transaction.product attributes in order to group them
We $group by branch, department and product values.
At the end, we transform into the desired output with the $replaceRoot operator.

db.collection.aggregate([
  {
    $unwind: "$transaction"
  },
  {
    $unwind: "$transaction.product"
  },
  {
    $group: {
      _id: {
        branch: "$branch",
        department: "$department",
        product: "$transaction.product.product"
      },
      quantity: {
        $sum: "$transaction.product.quantity"
      }
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$_id",
          {
            "quantity": "$quantity"
          }
        ]
      }
    }
  }
])

MongoPlayground

edited Jul 5, 2020 at 8:22

answered Jul 4, 2020 at 9:08

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LeoGarcia Over a year ago

I edited my database to include other objects in product. My desired result is still the same as if to ignore "color" and "size". What should I do?

Valijon Over a year ago

@LeoGarcia Now, it's very easy to group, check my answer again

Collectives™ on Stack Overflow

MongoDB nested array group by Multiple values

1 Answer 1

Explanation

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related