Navigation

This version of the documentation is archived and no longer supported.

Aggregation >
Aggregation Framework Examples

Aggregation Framework Examples¶

On this page

Requirements
Aggregations using the Zip Code Data Set
Aggregation with User Preference Data

MongoDB provides flexible data aggregation functionality with the aggregate command. For additional information about aggregation consider the following resources:

This document provides a number of practical examples that display the capabilities of the aggregation framework. All examples use a publicly available data set of all zipcodes and populations in the United States.

Requirements¶

mongod and mongo, version 2.2 or later.

Aggregations using the Zip Code Data Set¶

To run you will need the zipcode data set. These data are available at: media.mongodb.org/zips.json. Use mongoimport to load this data set into your mongod instance.

Data Model¶

Each document in this collection has the following form:

{
  "_id": "10280",
  "city": "NEW YORK",
  "state": "NY",
  "pop": 5574,
  "loc": [
    -74.016323,
    40.710537
  ]
}

In these documents:

The _id field holds the zipcode as a string.
The city field holds the city.
The state field holds the two letter state abbreviation.
The pop field holds the population.
The loc field holds the location as a latitude longitude pair.

All of the following examples use the aggregate() helper in the mongo shell. aggregate() provides a wrapper around the aggregate database command. See the documentation for your driver for a more idiomatic interface for data aggregation operations.

States with Populations Over 10 Million¶

To return all states with a population greater than 10 million, use the following aggregation operation:

db.zipcodes.aggregate( { $group :
                         { _id : "$state",
                           totalPop : { $sum : "$pop" } } },
                       { $match : {totalPop : { $gte : 10*1000*1000 } } } )

Aggregations operations using the aggregate() helper, process all documents on the zipcodes collection. aggregate() connects a number of pipeline operators, which define the aggregation process.

In the above example, the pipeline passes all documents in the zipcodes collection through the following steps:

the $group operator collects all documents and creates documents for each state.

These new per-state documents have one field in addition the _id field: totalPop which is a generated field using the $sum operation to calculate the total value of all pop fields in the source documents.

After the $group operation the documents in the pipeline resemble the following:
copy
```
{
  "_id" : "AK",
  "totalPop" : 550043
}
```
the $match operation filters these documents so that the only documents that remain are those where the value of totalPop is greater than or equal to 10 million.

The $match operation does not alter the documents, which have the same format as the documents output by $group.

The equivalent SQL for this operation is:

SELECT state, SUM(pop) AS pop
       FROM zips
       GROUP BY state
       HAVING pop > (10*1000*1000)

Average City Population by State¶

To return the average populations for cities in each state, use the following aggregation operation:

db.zipcodes.aggregate( { $group :
                         { _id : { state : "$state", city : "$city" },
                           pop : { $sum : "$pop" } } },
                       { $group :
                       { _id : "$_id.state",
                         avgCityPop : { $avg : "$pop" } } } )

Aggregations operations using the aggregate() helper, process all documents on the zipcodes collection. aggregate() a number of pipeline operators that define the aggregation process.

In the above example, the pipeline passes all documents in the zipcodes collection through the following steps:

the $group operator collects all documents and creates new documents for every combination of the city and state fields in the source document.

After this stage in the pipeline, the documents resemble the following:
copy
```
{
  "_id" : {
    "state" : "CO",
    "city" : "EDGEWATER"
  },
  "pop" : 13154
}
```
the second $group operator collects documents by the state field and use the $avg expression to compute a value for the avgCityPop field.

The final output of this aggregation operation is:

{
  "_id" : "MN",
  "avgCityPop" : 5335
},

Largest and Smallest Cities by State¶

To return the smallest and largest cities by population for each state, use the following aggregation operation:

db.zipcodes.aggregate( { $group:
                         { _id: { state: "$state", city: "$city" },
                           pop: { $sum: "$pop" } } },
                       { $sort: { pop: 1 } },
                       { $group:
                         { _id : "$_id.state",
                           biggestCity:  { $last: "$_id.city" },
                           biggestPop:   { $last: "$pop" },
                           smallestCity: { $first: "$_id.city" },
                           smallestPop:  { $first: "$pop" } } },

                       // the following $project is optional, and
                       // modifies the output format.

                       { $project:
                         { _id: 0,
                           state: "$_id",
                           biggestCity:  { name: "$biggestCity",  pop: "$biggestPop" },
                           smallestCity: { name: "$smallestCity", pop: "$smallestPop" } } } )

Aggregations operations using the aggregate() helper, process all documents on the zipcodes collection. aggregate() a number of pipeline operators that define the aggregation process.

All documents from the zipcodes collection pass into the pipeline, which consists of the following steps:

the $group operator collects all documents and creates new documents for every combination of the city and state fields in the source documents.

By specifying the value of _id as a sub-document that contains both fields, the operation preserves the state field for use later in the pipeline. The documents produced by this stage of the pipeline have a second field, pop, which uses the $sum operator to provide the total of the pop fields in the source document.

At this stage in the pipeline, the documents resemble the following:
copy
```
{
  "_id" : {
    "state" : "CO",
    "city" : "EDGEWATER"
  },
  "pop" : 13154
}
```
$sort operator orders the documents in the pipeline based on the value of the pop field from smallest to largest. This operation does not alter the documents.
the second $group operator collects the documents in the pipeline by the state field, which is a field inside the nested _id document.

Within each per-state document this $group operator specifies four fields: Using the $last expression, the $group operator creates the biggestcity and biggestpop fields that store the city with the largest population and that population. Using the $first expression, the $group operator creates the smallestcity and smallestpop fields that store the city with the smallest population and that population.

The documents, at this stage in the pipeline resemble the following:
copy
```
{
  "_id" : "WA",
  "biggestCity" : "SEATTLE",
  "biggestPop" : 520096,
  "smallestCity" : "BENGE",
  "smallestPop" : 2
}
```
The final operation is $project, which renames the _id field to state and moves the biggestCity, biggestPop, smallestCity, and smallestPop into biggestCity and smallestCity sub-documents.

The final output of this aggregation operation is:

{
  "state" : "RI",
  "biggestCity" : {
    "name" : "CRANSTON",
    "pop" : 176404
  },
  "smallestCity" : {
    "name" : "CLAYVILLE",
    "pop" : 45
  }
}

Aggregation with User Preference Data¶

Data Model¶

Consider a hypothetical sports club with a database that contains a user collection that tracks user’s join dates, sport preferences, and stores these data in documents that resemble the following:

{
  _id : "jane",
  joined : ISODate("2011-03-02"),
  likes : ["golf", "racquetball"]
}
{
  _id : "joe",
  joined : ISODate("2012-07-02"),
  likes : ["tennis", "golf", "swimming"]
}

Normalize and Sort Documents¶

The following operation returns user names in upper case and in alphabetical order. The aggregation includes user names for all documents in the users collection. You might do this to normalize user names for processing.

db.users.aggregate(
  [
    { $project : { name:{$toUpper:"$_id"} , _id:0 } },
    { $sort : { name : 1 } }
  ]
)

All documents from the users collection passes through the pipeline, which consists of the following operations:

The $project operator:
- creates a new field called name.
- converts the value of the _id to upper case, with the $toUpper operator. Then the $project creates a new field, named name to hold this value.
- suppresses the id field. $project will pass the _id field by default, unless explicitly suppressed.
The $sort operator orders the results by the name field.

The results of the aggregation would resemble the following:

{
  "name" : "JANE"
},
{
  "name" : "JILL"
},
{
  "name" : "JOE"
}

Return Usernames Ordered by Join Month¶

The following aggregation operation returns user names sorted by the month they joined. This kind of aggregation could help generate membership renewal notices.

db.users.aggregate(
  [
    { $project : { month_joined : {
                                    $month : "$joined"
                                  },
                   name : "$_id",
                   _id : 0
                 },
    { $sort : { month_joined : 1 } }
  ]
)

The pipeline passes all documents in the users collection through the following operations:

The $project operator:
- Creates two new fields: month_joined and name.
- Suppresses the id from the results. The aggregate() method includes the _id, unless explicitly suppressed.
The $month operator converts the values of the joined field to integer representations of the month. Then the $project operator assigns those values to the month_joined field.
The $sort operator sorts the results by the month_joined field.

The operation returns results that resemble the following:

{
  "month_joined" : 1,
  "name" : "ruth"
},
{
  "month_joined" : 1,
  "name" : "harold"
},
{
  "month_joined" : 1,
  "name" : "kate"
}
{
  "month_joined" : 2,
  "name" : "jill"
}

Return Total Number of Joins per Month¶

The following operation shows how many people joined each month of the year. You might use this aggregated data for such information for recruiting and marketing strategies.

db.users.aggregate(
  [
    { $project : { month_joined : { $month : "$joined" } } } ,
    { $group : { _id : {month_joined:"$month_joined"} , number : { $sum : 1 } } },
    { $sort : { "_id.month_joined" : 1 } }
  ]
)

The pipeline passes all documents in the users collection through the following operations:

The $project operator creates a new field called month_joined.
The $month operator converts the values of the joined field to integer representations of the month. Then the $project operator assigns the values to the month_joined field.
The $group operator collects all documents with a given month_joined value and counts how many documents there are for that value. Specifically, for each unique value, $group creates a new “per-month” document with two fields:
- _id, which contains a nested document with the month_joined field and its value.
- number, which is a generated field. The $sum operator increments this field by 1 for every document containing the given month_joined value.
The $sort operator sorts the documents created by $group according to the contents of the month_joined field.

The result of this aggregation operation would resemble the following:

{
  "_id" : {
    "month_joined" : 1
  },
  "number" : 3
},
{
  "_id" : {
    "month_joined" : 2
  },
  "number" : 9
},
{
  "_id" : {
    "month_joined" : 3
  },
  "number" : 5
}

Return the Five Most Common “Likes”¶

The following aggregation collects top five most “liked” activities in the data set. In this data set, you might use an analysis of this to help inform planning and future development.

db.users.aggregate(
  [
    { $unwind : "$likes" },
    { $group : { _id : "$likes" , number : { $sum : 1 } } },
    { $sort : { number : -1 } },
    { $limit : 5 }
  ]
)

The pipeline begins with all documents in the users collection, and passes these documents through the following operations:

The $unwind operator separates each value in the likes array, and creates a new version of the source document for every element in the array.

Example

Given the following document from the users collection:

{
  _id : "jane",
  joined : ISODate("2011-03-02"),
  likes : ["golf", "racquetball"]
}

The $unwind operator would create the following documents:

{
  _id : "jane",
  joined : ISODate("2011-03-02"),
  likes : "golf"
}
{
  _id : "jane",
  joined : ISODate("2011-03-02"),
  likes : "racquetball"
}

The $group operator collects all documents the same value for the likes field and counts each grouping. With this information, $group creates a new document with two fields:
- _id, which contains the likes value.
- number, which is a generated field. The $sum operator increments this field by 1 for every document containing the given likes value.
The $sort operator sorts these documents by the number field in reverse order.
The $limit operator only includes the first 5 result documents.

The results of aggregation would resemble the following:

{
  "_id" : "golf",
  "number" : 33
},
{
  "_id" : "racquetball",
  "number" : 31
},
{
  "_id" : "swimming",
  "number" : 24
},
{
  "_id" : "handball",
  "number" : 19
},
{
  "_id" : "tennis",
  "number" : 18
}

← Aggregation Framework Aggregation Framework Reference →