How to group the mondoDB document data by timestamp as quarterly/half yearly

Question

I am using MongoDB 3.2 and I am having requirement of grouping the document by timstamp as quarterly and half-yearly. The document structure is like

{
    "_id" : ObjectId("59312c59bf501118aea587b2"),
    "timestamp" : ISODate("2012-01-01T01:00:00Z"),
    "value" : 20,
    "uniqueId" : ObjectId("59312c59bf501118aea58a6d")
},
{
    "_id" : ObjectId("59312c59bf501118aea587b3"),
    "timestamp" : ISODate("2012-02-01T01:00:00Z"),
    "value" : 20,
    "uniqueId" : ObjectId("59312c59bf501118aea58a6d")
},
{
    "_id" : ObjectId("59312c59bf501118aea587b4"),
    "timestamp" : ISODate("2012-05-01T01:00:00Z"),
    "value" : 20,
    "uniqueId" : ObjectId("59312c59bf501118aea58a6d")
},
{
    "_id" : ObjectId("59312c59bf501118aea587b5"),
    "timestamp" : ISODate("2012-06-01T01:00:00Z"),
    "value" : 20,
    "uniqueId" : ObjectId("59312c59bf501118aea58a6d")
}

I need to group the document by timestamp quarterly or half yearly and I need to sum the value . The result for quarterly should looks like below

{
    "timestamp" : ISODate("2012-01-01T01:00:00Z"),
    "value" : 40
}, 
{
    "timestamp" :  ISODate("2012-05-01T01:00:00Z"),
    "value" : 90
}

Can any help how can I achieve this and also for the half-yearly.

Sergey Berezovskiy · Accepted Answer · 2017-06-02T12:12:42.523

You can aggregate documents by quarter, but the calculating first date of each quarter should be done on the client side:

db.yourCollection.aggregate([
   {
       $group: {
           _id: {
               year: {$year: "$timestamp"},
               quarter: {$ceil: {$divide:[{$month:"$timestamp"}, 3]}}
               },
           value: {$sum:"$value"}
        }        
    },
    { $project: { _id: 0, year: "$_id.year", quarter: "$_id.quarter", value: 1 } },
    { $sort: { year: 1, quarter: 1 } }
])

Output:

{
    "year" : 2012,
    "quarter" : 1,
    "value" : 40
}
,
{
    "year" : 2012,
    "quarter" : 2,
    "value" : 40
}

If you want half-year reports, then insted of division by 3 you should use division by 6.

How to group the mondoDB document data by timestamp as quarterly/half yearly

1 Answers1