Sum in each floor by category in aggregation

Other MongoDB Topics Open Source Projects
,
8

With a easy Saturday morning I have updated the solution with the cosmetic stages I mentioned. I also added the processing of the first and last date, a requirement I missed from the original post.

function match()
{
   return { "$match" : {
      "User" : "Edvin"
   } }
}

function sort()
{
   return { "$sort" : {
      "Date" : 1
   } }
}

function group()
{
   return { "$group" : {
      "_id" : null ,
      "_tmp_cost" : { "$push" : {
         "_group_city" : "$City" ,
         "_group_date" : "$Date" ,
         "_group_cost" : "$Cost"
      } }
   } }
}
function reduce()
{
   return { "$set" : { "_tmp_legs" : { "$reduce" : {
      "input" : "$_tmp_cost" ,
      "initialValue" : { "_city" : null , "_legs" : [] , "_cost" : 0 } ,
      "in" : { "$cond" : {
         "if" : { "$eq" : [ "$$this._group_city" , "$$value._city" ] } ,
         "then" : {
            "_city" : "$$this._group_city" ,
            "_legs" : "$$value._legs" ,
            "_first" : "$$value._first" ,
            "_last" : "$$this._group_date" ,
            "_cost" : { "$sum" : [ "$$value._cost" , "$$this._group_cost" ] }
         } ,
         "else" : {
            "_city" : "$$this._group_city" ,
            "_legs" : { "$concatArrays" : [
               "$$value._legs" ,
               [ { "_city" : "$$value._city" ,
                  "_cost" : "$$value._cost" ,
                  "_first" : "$$value._first" ,
                  "_last" : "$$value._last" ,
               } ]
            ] } ,
            "_first" : "$$this._group_date" ,
            "_last" : "$$this._group_date" ,
            "_cost" : "$$this._group_cost"
         }
      } }
   } } } } ;
}
function set_last_leg()
{
   return { "$set" : {
      "_legs" : { "$concatArrays" : [
         { "$slice" : [
             "$_tmp_legs._legs" ,
             1 ,
             { "$size" : "$_tmp_legs._legs" }
        ] } ,
         [ {
            "_city" : "$_tmp_legs._city" ,
            "_cost" : "$_tmp_legs._cost" ,
            "_first" : "$_tmp_legs._first" ,
            "_last" : "$_tmp_legs._last"
         } ]
      ] }
   } } ;
}

function cleanup()
{
   return { "$project" : {
      "_id" : 0 ,
      "_legs" : "$_legs"
   } } ;
}

function unwind()
{
   return { "$unwind" : "$_legs" }
}
function format()
{
   return { "$project" : {
      "_id" : 0 ,
      "City" : "$_legs._city" ,
      "Cost" : "$_legs._cost" ,
      "First" : "$_legs._first" ,
      "Last" : "$_legs._last" ,
   } } ;
}

function pipeline()
{
   return [
      match() ,
      sort() ,
      group() ,
      reduce() ,
      set_last_leg() ,
      cleanup() ,
      unwind() ,
      format()
   ] ;
}

And for those who have difficulties with modular code, this gives the big monolithic pipeline:

[
{ "$match" : {
      "User" : "Edvin"
   } } ,
{ "$sort" : {
      "Date" : 1
   } } ,
{ "$group" : {
      "_id" : null ,
      "_tmp_cost" : { "$push" : {
         "_group_city" : "$City" ,
         "_group_date" : "$Date" ,
         "_group_cost" : "$Cost"
      } }
   } } ,
{ "$set" : { "_tmp_legs" : { "$reduce" : {
      "input" : "$_tmp_cost" ,
      "initialValue" : { "_city" : null , "_legs" : [] , "_cost" : 0 } ,
      "in" : { "$cond" : {
         "if" : { "$eq" : [ "$$this._group_city" , "$$value._city" ] } ,
         "then" : {
            "_city" : "$$this._group_city" ,
            "_legs" : "$$value._legs" ,
            "_first" : "$$value._first" ,
            "_last" : "$$this._group_date" ,
            "_cost" : { "$sum" : [ "$$value._cost" , "$$this._group_cost" ] }
         } ,
         "else" : {
            "_city" : "$$this._group_city" ,
            "_legs" : { "$concatArrays" : [
               "$$value._legs" ,
               [ { "_city" : "$$value._city" ,
                  "_cost" : "$$value._cost" ,
                  "_first" : "$$value._first" ,
                  "_last" : "$$value._last" ,
               } ]
            ] } ,
            "_first" : "$$this._group_date" ,
            "_last" : "$$this._group_date" ,
            "_cost" : "$$this._group_cost"
         }
      } }
   } } } } ,
{ "$set" : {
      "_legs" : { "$concatArrays" : [
         { "$slice" : [ "$_tmp_legs._legs" , 1 , { "$size" : "$_tmp_legs._legs" } ] } ,
         [ {
            "_city" : "$_tmp_legs._city" ,
            "_cost" : "$_tmp_legs._cost" ,
            "_first" : "$_tmp_legs._first" ,
            "_last" : "$_tmp_legs._last"
         } ]
      ] }
   } } ,
{ "$project" : {
      "_id" : 0 ,
      "_legs" : "$_legs"
   } }  ,
{ "$unwind" : "$_legs" } ,
{ "$project" : {
      "_id" : 0 ,
      "City" : "$_legs._city" ,
      "Cost" : "$_legs._cost" ,
      "First" : "$_legs._first" ,
      "Last" : "$_legs._last" ,
   } }
]
1 Like