Morgodb aggregation framework Nested arrays subtract expression

Question

I have deep nested arrays, I'm trying to group some nested array elements and make this work. however, when I try to use the $ 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 correctly promoted for $ subtract, I tried $ project to reduce the fields in the pipeline and played with various combinations of $ project and $ group unsuccessfully. also tried unsuccessfully to relax.

 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 } 

Answer 1

A couple of problems:

• For each level of the nested array prior to timing you must

$unwind $subtract used with $project , not $group

Try the following:

 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 ] } ]