MongoDB aggregation by multiple fields

MongoDB offers aggregations operations which process data records and return computed results. Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result. MongoDB provides three ways to perform aggregation:

  • the aggregation pipeline,
  • the map-reduce function,
  • single purpose aggregation methods.

Today, let’s add a real example of using the aggregation pipeline.

Definition

Aggregation Pipeline: MongoDB’s aggregation framework is modeled on the concept of data processing pipelines. Documents enter a multi-stage pipeline that transforms the documents into an aggregated result.

Example

Let’s say I have the following data set

/* 1 */
{
"_id" : "00063fd780c04e4284c10b58717930f1",
"entitlementSource" : "source1",
"grantDate" : ISODate("2016-10-01T07:22:51.627Z")
}

/* 2 */
{
"_id" : "00094d2845894468b0237746d23e39c9",
"entitlementSource" : "source2",
"grantDate" : ISODate("2016-10-01T00:00:00.000Z")
}

/* 3 */
{
"_id" : "000e057be4a949b2adb3b99150a4f705",
"entitlementSource" : "source1",
"grantDate" : ISODate("2016-10-02T13:01:50.237Z")
}

/* 4 */
{
"_id" : "000e97e17fd34e26bf534fbb32497e98",
"entitlementSource" : "source2",
"grantDate" : ISODate("2016-10-02T13:19:27.759Z")
}
/* 5 */
{
"_id" : "230e97e17fd34e26bf534fbb32423e98",
"entitlementSource" : "source2",
"grantDate" : ISODate("2016-10-02T14:19:27.759Z")
}

I want to get a report of the total number of entitlement granted between Oct 1 to Oct 2, grouped by the date (in YYYY-MM-DD format).

entitlementSource 2016-10-01 2016-10-02
source1 1 1
source2 1 2

So it’s actually group by two fields, first the date, second the source.
Here is the actual query

db.entitlement.aggregate(
[	
  {$match: {grantDate: {$gte: ISODate("2016-10-01 00:00:00"),$lt:ISODate("2016-10-03 00:00:00")}}}, 
  {$project : { day : {$substr: ["$grantDate", 0, 10] }, entitlementSource:1}},   
  {$group   : { 
              _id : {
                        day: "$day",
                        entitlementSource : "$entitlementSource"
	      },				
             total : { $sum : 1 }}
  },
  {$sort : {"_id.day" : 1} }
]);

 

(Visited 32 times, 1 visits today)

Leave a Reply