Nested aggregate pipeline for mongodb

Question

I have a mongodb collection with lots of documents in the follow format:

{
    "_id": {
        "$oid": "581f9eae05711c35f461d779"
    },
    "eventID": "Illinois84bc00edc91c24a95573",
    "type": "voteup",
    "update": "f3a44be56669e01cf02ed685c97451ba",
    "count": 1,
}

I need to be able to run an aggregate from my server on the collection and return a JSON object which:

Groups the data by eventID (there are many eventIDs), then groups the documents in those groups by update (there are many updates, each associated to an eventID), then for each eventID/Update group, sums the 'count' values of each of the documents which are of type "voteup" and "votedown".

So an actual output might look like:

{
    "events": [
    //  array of objects, one for each unique eventID
        {
            "eventID": "Idaho4532543trt3424f",
            "updates": [
            //  array of objects, one for each unique update
                {
                    "update": "rh43782ty738tywuioty34",
                    "voteup": 12,  // the number of documents with that eventID/update which are type "voteup"
                    "votedown": 1  // the number of documents with that eventID/update which are type "voteudown"
                },
                {
                    "update": "hfu89h834hw8uoglthyw78",
                    "voteup": 2,
                    "votedown": 32
                },
                {
                    "update": "as09g8fgdsiuhgofhdosug",
                    "voteup": 123,
                    "votedown": 5
                }
            ]
        },
        //  here's another event...
        {
            "eventID": "Texas38tr943uytwo9grs",
            "updates": [
                {
                    "update": "2ty738tywuioty34rh4378",
                    "voteup": 0,
                    "votedown": 1
                },
                {
                    "update": "34hw8uoglthyw78hfu89hv",
                    "voteup": 22,
                    "votedown": 72
                },
                {
                    "update": "dsiuhgofhdosugas09g8fg",
                    "voteup": 67,
                    "votedown": 11
                }
            ]
        }
    ]
}

The following does the job of counting how many eventID/update/type documents are:

{
    $group: {

        _id: { eventID: "$eventName", updateID: "$update", sentiment: "$type" }

    }
},

But the output is simply a flat array of each. Is there a way to have aggregate produce a nested object like the one above?

Kevin Sandow · Accepted Answer · 2020-08-18 18:02:40Z

5

First you could group on eventID and update, and then group on eventID, while doing $push the update into an array

{$group: {
    _id: {eventID: '$eventID', update: '$update'},
    voteup: {$sum: {$cond: [
            {$eq: ['$type', {$literal: 'voteup'}]},
            1,
            0
        ]}},
    votedown: {$sum: {$cond: [
            {$eq: ['$type', {$literal: 'voteup'}]},
            0,
            1
        ]}}
}},

{$group: {
    _id: {eventID: '$_id.eventID'},
    updates: {$push: {
        update: '$_id.update',
        voteup: '$voteup',
        votedown: '$votedown'
    }}
}}

edited Aug 18, 2020 at 18:02

Kevin Sandow

4,0331 gold badge22 silver badges33 bronze badges

answered Nov 12, 2016 at 8:24

hyades

3,2101 gold badge22 silver badges36 bronze badges

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

2 Comments

Squidinker Over a year ago

One tiny error in there; in the second $group the $id.eventID needs to be $_id.eventID.

Squidinker Over a year ago

...but your solution is exactly what I was after. Thank you!

Collectives™ on Stack Overflow

Nested aggregate pipeline for mongodb

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related