Aggregate subdocuments in the parent document

Question

I have the following data:

{ 
    "make" : "Toyota", 
    "name" : "Corolla", 
    "year" : "2012"
    "color" : "Blue"
}
{ 
    "make" : "Toyota", 
    "name" : "Camry", 
    "year" : "2016"
    "color" : "Blue"
}
{ 
    "make" : "Toyota", 
    "name" : "Prius", 
    "year" : "2012"
    "color" : "Black"
}
{ 
    "make" : "Nissan", 
    "name" : "PathFinder", 
    "year" : 2012.0
    "color" : "Black"
}
{ 
    "make" : "Nissan", 
    "name" : "Qashqai", 
    "year" : 2011.0
    "color" : "Black"
}
{ 
    "make" : "Nissan", 
    "name" : "X-Trail", 
    "year" : 2009.0
    "color" : "Blue"
}

I want to write an aggregated query to a group make, and then colorto a group make.

So an excerpt from my desired result would look like this:

[{
    _id: "Toyota"
    colors: [
        {
            _id: "Black"
            cars: [{
                "make" : "Toyota", 
                "name" : "Prius", 
                "year" : "2012"
                "color" : "Black"
            }]
        },
        {
            _id: "Blue"
            cars:[{ 
                "make" : "Toyota", 
                "name" : "Corolla", 
                "year" : "2012"
                "color" : "Blue"
            },
            { 
                "make" : "Toyota", 
                "name" : "Camry", 
                "year" : "2016"
                "color" : "Blue"
            }]
        }
    ]
},
{
.... Nissan cars
}]

I tried the next pipeline and came close to my desired result, but not perfect

db.Cars.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $group: {
                _id: {make: '$make', color: '$color'},
                cars: {$push: '$$CURRENT'}
            }
        },

        // Stage 2
        {
            $group: {
                _id: '$_id.make',
                colors: {$push: '$$CURRENT'}
            }
        },
    ]
);

This leads to this (note the _idcolor box ):

[{ 
    "_id" : "Toyota", 
    "colors" : [
        {
            "_id" : {
                "make" : "Toyota", 
                "color" : "Black"
            }, 
            "cars" : [
                {
                    "make" : "Toyota", 
                    "name" : "Prius", 
                    "year" : "2012", 
                    "color" : "Black"
                }
            ]
        }, 
        {
            "_id" : {
                "make" : "Toyota", 
                "color" : "Blue"
            }, 
            "cars" : [
                {
                    "make" : "Toyota", 
                    "name" : "Corolla", 
                    "year" : "2012", 
                    "color" : "Blue"
                }, 
                {
                    "make" : "Toyota", 
                    "name" : "Camry", 
                    "year" : "2016", 
                    "color" : "Blue"
                }
            ]
        }
    ]
}
...
]

Any help when writing a query to approximate the output to the desired result will be pleasant

Answer 1

add stage $projector $addFieldsin $map colors._idfromcolors._id.color

the pipeline

db.cars.aggregate(
    [
        {
            $group: {
                _id: {make: '$make', color: '$color'},
                cars: {$push: '$$CURRENT'}
            }
        },
        {
            $group: {
                _id: '$_id.make',
                colors: {$push: '$$CURRENT'}
            }
        },
        {$project : {colors : {$map : {input : "$colors", as : "color", in : { "_id" : "$$color._id.color", cars : "$$color.cars" }}}}}

    ]
).pretty()

Output

>db.cars.aggregate( [ {$project : {_id : 0}}, { $group: { _id: {make: '$make', color: '$color'}, cars: {$push: '$$CURRENT'} } }, { $group: { _id: '$_id.make', colors: {$push: '$$CURRENT'} } }, {$project : {colors : {$map : {input : "$colors", as : "color", in : { "_id" : "$$color._id.color", cars : "$$color.cars" }}}}} ] ).pretty()

{
    "_id" : "Toyota",
    "colors" : [
        {
            "_id" : "Black",
            "cars" : [
                {
                    "make" : "Toyota",
                    "name" : "Prius",
                    "year" : "2012",
                    "color" : "Black"
                }
            ]
        },
        {
            "_id" : "Blue",
            "cars" : [
                {
                    "make" : "Toyota",
                    "name" : "Corolla",
                    "year" : "2012",
                    "color" : "Blue"
                },
                {
                    "make" : "Toyota",
                    "name" : "Camry",
                    "year" : "2016",
                    "color" : "Blue"
                }
            ]
        }
    ]
}
....//output omitted