Grouping into array in MongoDB

Question

I have MongoDB collection with below documents:

[
   {
      "productType":"Bike",
      "company":"yamaha",
      "model":"y1"
   },
   {
      "productType":"Bike",
      "company":"bajaj",
      "model":"b1"
   },
   {
      "productType":"Bike",
      "company":"yamaha",
      "model":"y1"
   },
   {
      "productType":"Car",
      "company":"Maruti",
      "model":"m1"
   },
   {
      "productType":"Bike",
      "company":"yamaha",
      "model":"y2"
   },
   {
      "productType":"Car",
      "company":"Suzuki",
      "model":"s1"
   }
]

I want my output to be like :

{
    "productType": [
        {
            "name": "Bike",
            "count": 4,
            "companies": [
                {
                    "name": "Yamaha",
                    "count": 3,
                    "models": [
                        {
                            "name": "y1",
                            "count": 2
                        },
                        {
                            "name": "y2",
                            "count": 1
                        }
                    ]
                },
                {
                    "name": "Bajaj",
                    "count": 1,
                    "models": [
                        {
                            "name": "b1",
                            "count": 1
                        }
                    ]
                }
            ]
        },
        {
            "name": "Car",
            "count": 2,
            "companies": [
                {
                    "name": "Maruti",
                    "count": 1,
                    "models": [
                        {
                            "name": "m1",
                            "count": 1
                        }
                    ]
                },
                {
                    "name": "Suzuki",
                    "count": 1,
                    "models": [
                        {
                            "name": "s1",
                            "count": 1
                        }
                    ]
                }
            ]
        }
    ]
}

I am not able to understand how to create arrays inside existing array using $push. I know we can create an array using $push but how to create array of array with it ? In future, I might want to add "metaData" field also along with name and count.

Is this a valid json object? Or you ave an array of objects? share valid data set. How does your collection look like? — Sohan
– Sohan, Commented Mar 22, 2021 at 7:00

Wernfried Domscheit · Accepted Answer · 2021-03-22 08:13:42Z

2

You have to run multiple $group stages, one for each level:

db.collection.aggregate([
   {
      $group: {
         _id: { company: "$company", productType: "$productType", model: "$model" },
         count: { $sum: 1 }
      }
   },
   {
      $group: {
         _id: { productType: "$_id.productType", company: "$_id.company" },
         models: { $push: { name: "$_id.model", count: "$count" } },
         count: { $sum: "$count" }
      }
   },
   {
      $group: {
         _id: "$_id.productType",
         companies: { $push: { company: "$_id.company", models: "$models", count: "$count" } },
         count: { $sum: "$count" }
      }
   },
   { $set: { name: "$_id", _id: "$$REMOVE" } },
   {
      $group: {
         _id: null,
         productType: { $push: "$$ROOT" }
      }
   }
])

Mongo Playground

answered Mar 22, 2021 at 8:13

Wernfried Domscheit

60.4k10 gold badges92 silver badges132 bronze badges

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

2 Comments

Anmol Jain Over a year ago

Thank You. Can you explain the thought process how you formed this query ?

Wernfried Domscheit Over a year ago

You have to start from leaf sub-document (models: [...]) and then go step by step up to the root level. (productType: ...). Start the aggregation with the first stage and have a look at the result. Then add the next stage and have again a look at the result. Continue with each stage one by one.

Dheemanth Bhat · Accepted Answer · 2021-03-22 10:02:11Z

Try this:

db.testCollection.aggregate([
    {
        $group: {
            _id: {
                name: "$productType",
                company: "$company",
                model: "$model"
            },
            count: { $sum: 1 }
        }
    },
    {
        $group: {
            _id: {
                name: "$_id.name",
                company: "$_id.company"
            },
            count: { $sum: "$count" },
            models: {
                $push: {
                    name: "$_id.model",
                    count: "$count"
                }
            }
        }
    },
    {
        $group: {
            _id: { name: "$_id.name" },
            count: { $sum: "$count" },
            companies: {
                $push: {
                    name: "$_id.company",
                    count: "$count",
                    models: "$models"
                }
            }
        }
    },
    {
        $group: {
            _id: null,
            productType: {
                $push: {
                    name: "$_id.name",
                    count: "$count",
                    companies: "$companies"
                }
            }
        }
    },
    {
        $project: { _id: 0 }
    }
]);

Output:

{
    "productType" : [
        {
            "name" : "Car",
            "count" : 2,
            "companies" : [
                {
                    "name" : "Suzuki",
                    "count" : 1,
                    "models" : [
                        {
                            "name" : "s1",
                            "count" : 1
                        }
                    ]
                },
                {
                    "name" : "Maruti",
                    "count" : 1,
                    "models" : [
                        {
                            "name" : "m1",
                            "count" : 1
                        }
                    ]
                }
            ]
        },
        {
            "name" : "Bike",
            "count" : 4,
            "companies" : [
                {
                    "name" : "yamaha",
                    "count" : 3,
                    "models" : [
                        {
                            "name" : "y2",
                            "count" : 1
                        },
                        {
                            "name" : "y1",
                            "count" : 2
                        }
                    ]
                },
                {
                    "name" : "bajaj",
                    "count" : 1,
                    "models" : [
                        {
                            "name" : "b1",
                            "count" : 1
                        }
                    ]
                }
            ]
        }
    ]
}

Collectives™ on Stack Overflow

Grouping into array in MongoDB

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related