CODEWITHSUNDEEP
mongodb
Ronniel Sembrano
Ronniel Sembrano

Reputation: 96

querying date ranges without using loop in mongodb using aggregate

I have these array of date ranges. It's for a chart feature in my wep app.

[{
    "start": "7/01/2016",
    "end": "7/31/2016"
},{
    "start": "8/01/2016",
    "end": "8/31/2016"
},{
    "start": "9/01/2016",
    "end": "9/30/2016"
}]

This is my sample data.

    {
        "_id": 68,
        "charges": [
            {
                "id": "ch_1AD2wYHDsLEzoG2tjPo7uGnq",
                "amount": 1200,
               "created": "7/13/2016"
            },{
                "id": "ch_1ADPRPHDsLEzoG2t1k3o0qCz",
                "amount": 2000,
                "created": "8/1/2016"
            },{
                "id": "ch_1ADluFHDsLEzoG2t608Bppzn",
                "amount": 900,
                "created": "8/2/2016"
            },{
                "id": "ch_1AE8OWHDsLEzoG2tBmlm1A22",
                "amount": 1800,
                "created": "9/14/2016"
            }
        ]
    }

This is the result that I'm trying to achieve.

[
    {
        "created": "9/13/2016",
        "amount": 1200
    },{
        "created": "9/14/2016",
        "amount": 2900
    },{
        "created": "9/15/2016",
        "amount": 1800
    },
]

Can I achieve that without looping the date range and querying inside? I only manage to get this far

    [
        {
            $match: { _id: 68 }
        },{
            $unwind: "$charges"
        },{
            I don't know what to do here
        }
    ]

NOTE: Nevermind the date formatting

Upvotes: 1

Views: 664

Answers (1)

felix
felix

Reputation: 9295

you can achieve this using the new $bucket operator introduced in mongodb 3.4 liek this : 

db.collection.aggregate([  
   {  
      $match:{  
         _id:68
      }
   },
   {  
      $unwind:"$charges"
   },
   {  
      $bucket:{  
         groupBy:"$charges.created",
         boundaries:[  
            "7/01/2016",
            "8/01/2016",
            "9/01/2016",
            "9/30/2016"
         ],
         default:"others",
         output:{  
            amount:{  
               $sum:"$charges.amount"
            }
         }
      }
   }
])

explaination:

  1. match a specific document using $match
  2. unwind charges array
  3. group by range ( range is provided in boundaries)

this output: 

{ "_id" : "7/01/2016", "amount" : 1200 }
{ "_id" : "8/01/2016", "amount" : 2900 }
{ "_id" : "9/01/2016", "amount" : 1800 }

Upvotes: 1

Related Questions