How to aggregate this data effectively

Question

I am looking for aggregate the following data:

Full JSON: https://paste.ubuntu.com/24147839/

Example:

{
"_id": "58c1b957f6d187a57dd4a458",
"stats": {
  "2017": {
    "3": {
      "9": {
        "6": {
          "49": {
            "sum": {
              "clicks": 1,
              "cost": 0.01
            }
          },
          "sum": {
            "clicks": 1,
            "cost": 0.01
          }
        },
        "sum": {
          "clicks": 1,
          "cost": 0.01
        }
      }
    }
  }
}

The result I want to get is the following: (this is just a sample, it will be one for each hour)

 "6": { clicks: 686 , cost: 3.05399999999995 }" 

I took 6 out $stats.2017.3.9.6, and clicks are $stats.2017.3.9.6.sum.clicksand costis the amount$stats.2017.3.9.6.sum.cost

What aggregation query should use to get this result?

Thank you in advance for your time.

Answer 1

, ( 2017-03-09, , , . , :

db.coll.aggregate([
  {$match:{"stats.2017.3.9":{$exists:true}}},
  {$project:{hours:[
      {hour:"00", sum:"$stats.2017.3.9.0.sum"}, 
      {hour:"01", sum:"$stats.2017.3.9.1.sum"},
      {hour:"02", sum:"$stats.2017.3.9.2.sum"}, 
      {hour:"03", sum:"$stats.2017.3.9.3.sum"}, 
      {hour:"04", sum:"$stats.2017.3.9.4.sum"}, 
      {hour:"05", sum:"$stats.2017.3.9.5.sum"}, 
      {hour:"06", sum:"$stats.2017.3.9.6.sum"}, 
      {hour:"07", sum:"$stats.2017.3.9.7.sum"}, 
      {hour:"08", sum:"$stats.2017.3.9.8.sum"}, 
      {hour:"09", sum:"$stats.2017.3.9.9.sum"}, 
      {hour:"10", sum:"$stats.2017.3.9.10.sum"}, 
      {hour:"11", sum:"$stats.2017.3.9.11.sum"}, 
      {hour:"12", sum:"$stats.2017.3.9.12.sum"},   
      {hour:"13", sum:"$stats.2017.3.9.13.sum"},   
      {hour:"14", sum:"$stats.2017.3.9.14.sum"},   
      {hour:"15", sum:"$stats.2017.3.9.15.sum"},   
      {hour:"16", sum:"$stats.2017.3.9.16.sum"},   
      {hour:"17", sum:"$stats.2017.3.9.17.sum"},   
      {hour:"18", sum:"$stats.2017.3.9.18.sum"},   
      {hour:"19", sum:"$stats.2017.3.9.19.sum"},   
      {hour:"20", sum:"$stats.2017.3.9.20.sum"},   
      {hour:"21", sum:"$stats.2017.3.9.21.sum"},   
      {hour:"22", sum:"$stats.2017.3.9.22.sum"},   
      {hour:"23", sum:"$stats.2017.3.9.23.sum"}
  ]}}, 
  {$unwind:"$hours"},
  {$group: {
      _id    : "$hours.hour", 
      clicks : {$sum:"$hours.sum.clicks"},  
      cost   : {$sum:"$hours.sum.cost"}
  }}, 
  {$sort:{_id:1}}
])

, "stats.2017.3.9", , , . , . .

:

{ "_id" : "00", "clicks" : 93, "cost" : 0.419 }
{ "_id" : "01", "clicks" : 95, "cost" : 0.43 }
{ "_id" : "02", "clicks" : 86, "cost" : 0.427 }
{ "_id" : "03", "clicks" : 81, "cost" : 0.301 }
{ "_id" : "04", "clicks" : 92, "cost" : 0.423 }
{ "_id" : "05", "clicks" : 76, "cost" : 0.352 }
{ "_id" : "06", "clicks" : 91, "cost" : 0.397 }
{ "_id" : "07", "clicks" : 84, "cost" : 0.396 }
{ "_id" : "08", "clicks" : 95, "cost" : 0.353 }
{ "_id" : "09", "clicks" : 78, "cost" : 0.325 }
{ "_id" : "10", "clicks" : 100, "cost" : 0.40900000000000003 }
{ "_id" : "11", "clicks" : 96, "cost" : 0.405 }
{ "_id" : "12", "clicks" : 65, "cost" : 0.319 }
{ "_id" : "13", "clicks" : 90, "cost" : 0.395 }
{ "_id" : "14", "clicks" : 82, "cost" : 0.331 }
{ "_id" : "15", "clicks" : 85, "cost" : 0.38 }
{ "_id" : "16", "clicks" : 97, "cost" : 0.424 }
{ "_id" : "17", "clicks" : 27, "cost" : 0.125 }
{ "_id" : "18", "clicks" : 0, "cost" : 0 }
{ "_id" : "19", "clicks" : 0, "cost" : 0 }
{ "_id" : "20", "clicks" : 0, "cost" : 0 }
{ "_id" : "21", "clicks" : 0, "cost" : 0 }
{ "_id" : "22", "clicks" : 0, "cost" : 0 }
{ "_id" : "23", "clicks" : 0, "cost" : 0 }