mongodb aggregation framework nested arrays subtract expression

aggregation-framework, mongodb, node.js

Solution

Couple problems:

- You need to `$unwind` each nested array level all the way through to `timing`

- `$subtract` is used with `$project`, not `$group`

Try this:

collection.aggregate([
    {$unwind: "$scenes"},
    {$unwind: "$scenes.records"},
    {$unwind: "$scenes.records.timeProfile.timing"},
    {$match: {'scenes.records.timeProfile.timing.id' : 'SR-G'}},
    {$project: {
        segmentname: "$scenes.name",
        responseTime: {$subtract: ['$scenes.records.timeProfile.timing.stop', '$scenes.records.timeProfile.timing.start'] }
    }},
    {$group: {
        _id: '$segmentname',
        responseTimes: {$push: '$responseTime'}
    }}
], function (err, results) {
    console.log(results);
});

Outputs:

[ { _id: 'Greeting_Excited', responseTimes: [ 156 ] } ]

Problem

i have deep nested arrays, i am trying to group by some nested array elements and got this to work. however, when i try to use $subtract expression, it fails. any pointers appreciated. ``` Data: -scenes: [ -{ name: "Greeting_Excited" -records: [ - { type: "listeningCycle" listeningId: 2 timestamp: 1354566662041 -events: [ … ] -timeProfile: { -timeStampInfo: { earliestStamp: 1354566664530 latestStamp: 1354566678412 } -timing: [ -{ start: 400 stop: 556 id: "SR-G" } -{ start: 559 stop: 572 id: "NL-G" } ] } } ] } ] collection..aggregate( {$unwind:"$scenes"}, {$match: {'scenes.records.timeProfile.timing.id' : 'SR-G'}}, {$group: { _id : {segmentname: "$scenes.name"} , responsetimes : { $push : {$subtract : ["$scenes.records.timeProfile.timing.stop", "$scenes.records.timeProfile.timing.start"]} }}}, {$sort:{responsetimes:1}} I am using the mongodb 2.2.1 and native node mongodb driver 1.1.11. what i am trying to do: -group by scenes [unwind the scenes array], -for a match with SR-G timing-id, -gather all the response times, hence the $subtract (stop-start). This is the error msg i see: { "errmsg" : "exception: can't convert from BSON type Array to long", "code" : 16004, "ok" : 0 } ``` it seems that the innermost nested array: $scenes.records.timeProfile.timing is not unwound correctly for $subtract, i tried $project to reduce the fields in the pipeline and played around with various combinations of $project and $group unsuccessfully. also tried to unwind more than once, unsuccessfully. ``` collection.aggregate( {$project: {segmentname:"$scenes.name", timingid: "$scenes.records.timeProfile.timing.id", timingstart:"$scenes.records.timeProfile.timing.start", timingstop:"$scenes.records.timeProfile.timing.stop"}}, {$unwind:"$scenes"}, {$match: {'scenes.records.timeProfile.timing.id' : 'SR-G'}} ) { "result" : [ ], "ok" : 1 } ``` and ``` collection.aggregate( {$unwind: "$scenes"}, {$project: {segmentname:"$scenes.name", timingid: "$scenes.records.timeProfile.timing.id", timingstart:"$scenes.records.timeProfile.timing.start", timingstop:"$scenes.records.timeProfile.timing.stop"}}, {$unwind:"$scenes.records.timeProfile.timing"}, {$match: { "scenes.records.timeProfile.timing.id" : "SR-G"}}, {$project: {segmentname:"$scenes.name", timingid: "$scenes.records.timeProfile.timing.id", timingstart:"$scenes.records.timeProfile.timing.start", timingstop:"$scenes.records.timeProfile.timing.stop"}} ) { "result" : [ ], "ok" : 1 } ```

Original source