javascript - How to compare grouped data between two dates using the mongodb aggregate framework -


consider data set follows

sector value date      2     1/1/2015 b      5     1/1/2015 c      8     1/1/2015       3     1/1/2015      6     1/1/2015      1     1/1/2015 c      1     1/1/2015  sector value date      2     2/1/2015 b      10    2/1/2015 b      5     2/1/2015 c      8     2/1/2015 

is there way use mongodb aggregate framework find difference between each grouped sector 2 dates in question?

in other words possible output this:

a    -> (2+3+6) - 2 = 10  b    -> 5 - (10+5) = -10 c    -> (8+1) - 8 = 1 

using aggregation framework , $cond operator change sign of value on 2/1/2015, simple sum.

given test collection:

{ name: 'a',      value: 2,     date: isodate('2015-01-01') }, { name: 'b',      value: 5,     date: isodate('2015-01-01') }, { name: 'c',      value: 8,     date: isodate('2015-01-01') }, { name: 'a',      value: 3,     date: isodate('2015-01-01') }, { name: 'a',      value: 6,     date: isodate('2015-01-01') }, { name: 'a',      value: 1,     date: isodate('2015-01-01') }, { name: 'c',      value: 1,     date: isodate('2015-01-01') }, { name: 'a',      value: 2,     date: isodate('2015-01-02') }, { name: 'b',      value: 10,    date: isodate('2015-01-02') }, { name: 'b',      value: 5,     date: isodate('2015-01-02') }, { name: 'c',      value: 8,     date: isodate('2015-01-02') } 

here how achieve desired result:

db.collection.aggregate([   { $match: { date: { $in: [ isodate('2015-01-01'), isodate('2015-01-02') ] }}},   { $project: { name: 1,                 value: { $cond: [ {$eq: [ "$date", isodate('2015-01-01')]},                                   "$value",                                   { $subtract: [0, "$value"] } ]}}},   { $group: { _id: "$name", total: { $sum: "$value" }}} ]) 
  • the $match stage keeps value given 2 days
  • the $project stage keep value as-is documents date equals '2015-01-01', change value sign documents date equals '2015-01-02'
  • finally, $group stage sum values (positive , negatives).

producing:

{ "_id" : "c", "total" : 1 } { "_id" : "b", "total" : -10 } { "_id" : "a", "total" : 10 } 

Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

jdbc - Not able to establish database connection in eclipse -

Kivy: Swiping (Carousel & ScreenManager) -