MongoDB Aggregation push zero in keys while doing group

Question

I am not sure that this is a valid question or not. I have started working on mongodb aggregation. I have to make a graph for the data on daily, weekly, monthly basis.

I am using "$dayOfMonth", "$week", "$month" to group by depending on the date provided. ex if from and to dates difference is less or equal to 6 I am grouping on daily basis using "$dayOfMonth", If from and to dates difference is greater than 6 and less than 30 grouping is done of "$week" and if differece is greater than 30 then grouping is done on monthly basis "$month".

I am passing date in my "$match". Is it possible to push 0 as keys if the gouping is not present.

example - from_date = "01/01/2018" to_date = "30/6/2018" so grouping will be done on month. and suppose if I dont have date for 3 and 4th & 5th month. I want to push 0 in the nested keys as the value.

    output = [
               {"_id": "01/01/2018", "counter":12}, 
               {"_id": "01/02/2018", "counter": 15}, 
               {"_id":"01/06/2018", counter: 10}
             ]

     expected_output = 
             [
              {"_id": "01/01/2018", "counter":12}, 
              {"_id": "01/02/2018", "counter": 15},
              {"_id":"01/03/2018", counter: 0},
              {"_id":"01/04/2018", counter:0},
              {"_id":"01/05/2018", counter: 0},
              {"_id":"01/06/2018", counter: 10}
             ]

I am using Rails and Mongoid Gem. Query That I am using

    converted = Analytics::Conversion::PharmacyPrescription.collection.aggregate([
       { "$match" => { 
            "organisation_id" => org_id.to_s, 
            "date" => {
              "$gte" => from_date, 
              "$lte" => to_date
            },
            "role_ids" => {"$in" => [role_id, "$role_ids"]}
          }
        },{
          "$project" => {
            "total_count" => 1,
            "converted_count" => 1,
            "not_converted_count" => 1,
            "total_invoice_amount" => 1,
            "user_id" => 1,
            "facility_id" => 1,
            "organisation_id" => 1,
            "date" => 1,
          }
        },{
          "$group" => {
            "_id" => { "#{groupby}" => "$date" },
            "total_count" => {"$sum" => "$total_count"},
            "converted_count" => { "$sum" => "$converted_count" },
            "not_converted_count" => { "$sum" => "$not_converted_count"},
          }
        }
    ]).to_a

score 0 · Answer 1 · answered Nov 08 '18 at 15:43

The Aggregation Framework can only aggregate the documents you have. You are actually asking it to add groups for documents that do not exist, but it has no way to "know" which groups to add.

What I would do is run the query as you have it, and afterwards "spread" the date units according to the chosen granularity (in your example it will be 01/01/2018, 01/02/2018, 01/03/2018, 01/04/2018, 01/05/2018, 01/06/2018, and run a simple function which will add an entry for each missing unit.

MongoDB Aggregation push zero in keys while doing group

1 Answers1