MongoDB aggregation, distinct several fields and count them

Question

The best way I can describe what I want to achieve is using an example. Basically I would have a list of cars say:

[
    {
        _id: 1,
        make: 'Toyota',
        model: 'Prius'
    },
    {
        _id: 2,
        make: 'Toyota',
        model: 'Prius'
    },
    {
        _id: 3,
        make: 'Toyota',
        model: 'Yaris'
    },
    {
        _id: 4,
        make: 'Lexus',
        model: 'RX400'
    }
]

And now I want to group/distinct them by make and model (and possibly more fields) and count the totals. The final result should look something like:

{
    makes: [
        {
            name: 'Toyota',
            total: 3
        }, {
            name: 'Lexus',
            total: 1
        }
    ],
    models: [
        {
            name: 'Prius',
            total: 2
        },
        {
            name: 'Yaris',
            total: 1
        },
        {
            name: 'RX400',
            total: 1
        }
    ]
}

I'm completely stuck with this one. So far, the only way I can achieve this is by calling several async aggregation calls for each field. However, I would prefer to do it in a single aggregation call, if that is possible at all (unless it's not a good idea performance wise).

chridam · Accepted Answer · 2018-04-17 14:15:15Z

2

Use $facet for this:

db.collection.aggregate([
    { "$facet": {
        "makes": [
            { "$group": {
                "_id": "$make",
                "total": { "$sum": 1 }
            } },
            { "$project": {
                "_id": 0,
                "name": "$_id",
                "total": 1
            } }  
        ],
        "models": [
            { "$group": {
                "_id": "$model",
                "total": { "$sum": 1 }
            } },
            { "$project": {
                "_id": 0,
                "name": "$_id",
                "total": 1
            } }  
        ]
    } }
])

edited Apr 17, 2018 at 14:15

answered Apr 17, 2018 at 14:10

chridam

104k26 gold badges246 silver badges243 bronze badges

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

3 Comments

Sede Over a year ago

You could add the "name" field in the $group stage and remove the $project stage

chridam Over a year ago

@styvane Hmmm am I missing something here since the name is the group by key so I suppose if the OP can do without the "name" key and use the _id key instead then no need for the $project step.

Sede Over a year ago

You can use the $first/$last operator to add the new field in the $group stage. Of course the use of one of these operators in this case is just a workaround for the fact that you cannot add new field in the $group stage.

Collectives™ on Stack Overflow

MongoDB aggregation, distinct several fields and count them

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related