mongodb aggregation - unwind/group/project query combination

Question

I have records in a collection of the following format.

//One parent record
{
    "_id" : "someDocID",
    "title" : "some title",
    "analytics" : [
            {
                    "_id" : "analyticsID1", 
                   "timeSpent" : [
                            {
                                    "time" : 14,
                                    "pageNo" : 1
                            },
                            {
                                    "time" : 4,
                                    "pageNo" : 2
                            },
                            {
                                    "time" : 3,
                                    "pageNo" : 1
                            },
                            {
                                    "time" : 1,
                                    "pageNo" : 2
                            }
                    ]                       

            },
            {                        
                    "_id" : "analyticsID2",                        
                    "timeSpent" : [
                            {
                                    "time" : 12,
                                    "pageNo" : 10
                            },
                            {
                                    "time" : 15,
                                    "pageNo" : 11
                            },
                            {
                                    "time" : 26,
                                    "pageNo" : 12
                            },
                            {
                                    "time" : 13,
                                    "pageNo" : 11
                            },
                            {
                                    "time" : 17,
                                    "pageNo" : 10
                            },
                            {
                                    "time" : 30,
                                    "pageNo" : 11
                            }
                    ]
            }
    ]               
}

The "pageNo" field contains repeated values. I need to group the pageNo field with adding their respective "time".

This is my required output. ( after "$unwind" operation on analytics )

//Two records after "$unwind" on analytics
{
    "_id" : "someDocID",
    "title" : "some title",
    "analytics" : {
                    "_id" : "analyticsID1", 
                    "timeSpent" : [
                            {
                                    "time" : 17,   //14+3
                                    "pageNo" : 1
                            },
                            {
                                    "time" : 5,    //4+1
                                    "pageNo" : 2
                            }
                    ]
            }
}

{
    "_id" : "someDocID",
    "title" : "some title",
    "analytics" : {
                    "_id" : "analyticsID2", 
                    "timeSpent" : [
                            {
                                    "time" : 29,    //12+17
                                    "pageNo" : 10
                            },
                            {
                                    "time" : 58,    //15+13+30
                                    "pageNo" : 11
                            },
                            {
                                    "time" : 26,
                                    "pageNo" : 12
                            }                                
                    ]      
            }
}

I've tried various combinations of aggregate, group, unwind and project but still can't quite get there and would really appreciate any suggestions.

What do you want to end with? Are you trying to sum "time" by "pageNo", or "time" by "pageNo" for each analytics ID? — Adam Harrison
I want something like this. analyticsID1 -> { timeSpent : [ { time : 10, pageNo: 1 },..] }, analyticsID2 -> { timeSpent : [ { time : 20, pageNo: 1 },..] } — Tarush Arora

Matthew Antolovich Matthew Antolovich · Accepted Answer · 2016-10-23T00:36:25

Here is an aggregate I came up with to provide the output that you mentioned in your comment above. As an FYI, the more elements you have in an array that needs to be unwound, the more memory usage you'll have, and it will take an exponentially amount of time based on array sizes. I would highly recommend you structure your data differently if your arrays are not limited in length.

var aggregrate = [{
    $unwind: '$analytics'
}, {
    $unwind: '$analytics.timeSpent'
}, {
    $group: {
        _id: {
            analytics_id: '$analytics._id',
            pageNo: '$analytics.timeSpent.pageNo'
        },
        title:{$first:'$title'},
        time: {
            $sum: '$analytics.timeSpent.time'
        },
    }
}, {
    $group: {
        _id: '$_id.analytics_id',
        title:{$first:'$title'},
        timeSpent: {
            $push: {
                time: '$time',
                pageNo: '$_id.pageNo'
            }
        }
    }
}, ];

This Outputs:

[{
    "_id": "analyticsID1",
    "title" : "some title", 
    "timeSpent": [{
        "time": NumberInt(17),
        "pageNo": NumberInt(1)
    }, {
        "time": NumberInt(5),
        "pageNo": NumberInt(2)
    }]
}, {
    "_id": "analyticsID2",
     "title" : "some title", 
     "timeSpent": [{
        "time": NumberInt(26),
        "pageNo": NumberInt(12)
    }, {
        "time": NumberInt(29),
        "pageNo": NumberInt(10)
    }, {
        "time": NumberInt(58),
        "pageNo": NumberInt(11)
    }]
}]

mongodb aggregation - unwind/group/project query combination

1 Answers