Navigation
This documentation refers to the MongoDB Charts service in MongoDB Atlas. Read the on-premises documentation to learn how to use the MongoDB Charts on site.

Backing Aggregation Pipeline

To get the data needed to render a chart, Charts creates a MongoDB Aggregation Pipeline which is executed on the MongoDB database server. The pipeline consists of multiple stages, each of which is generated based on different settings specified by the chart’s author. This document explains how the various Chart Builder settings are used to construct the Aggregation Pipeline. You can view the pipeline used to create a chart by choosing the View Aggregation Pipeline option in the Chart Builder’s ellipsis dropdown on the top right.

The pipeline constructed by MongoDB Charts consists of the following segments in the following order:

  1. Data Source Pipeline
  2. Dashboard Filters
  3. Query bar
  4. Embedding Filters
  5. Calculated Fields
  6. Chart Filter Pane
  7. Encoding
  8. Maximum Document limit

Note

You do not need to configure all of the above settings when creating a chart. Unspecified settings are skipped when generating the aggregation pipeline.

Note

Sorts and limits specified in the encoding panel are currently not included in the aggregation pipeline. Instead, the sorting and limiting is applied on the client-side while rendering the chart.

Example

The following chart shows the total sale amounts from an office supply company, categorized by purchase method. Each document in the data collection represents a single sale.

Using this chart as an example, we will explore how the specifications for each of the above settings change the aggregation pipeline generated by MongoDB Charts.

Aggregation pipline example without any filters.

Encoding

Without any Data Source pipeline, Query bar queries, calculated fields, and filters added in the Filter pane, MongoDB Charts generates the following aggregation pipeline:

{
  "$addFields": {                         //Encoding
    "__alias_0": {
      "$sum": "$items.price"
    }
  }
},
{
  "$group": {
    "_id": {
      "__alias_1": "$purchaseMethod"
    },
    "__alias_0": {
      "$sum": "$__alias_0"
    }
  }
},
{
  "$project": {
    "_id": 0,
    "__alias_1": "$_id.__alias_1",
    "__alias_0": 1
  }
},
{
  "$project": {
    "y": "$__alias_0",
    "x": "$__alias_1",
    "_id": 0
  }
},
{
  "$limit": 5000
}

The pipeline at this stage only consists of groups from the Encode panel and the maximum document limit, which is set to 5000 by MongoDB Charts.

Adding Queries

The query below restricts the documents shown to only those with a saleDate equal to or more recent than January 1, 2017 with at least 5 elements in the items array. items is an array where each element is an item purchased during a sale.

Query:

{
  $and: [
  {
    saleDate: { $gte: new Date("2017-01-01") }
  },
  {
    'items.4': { $exists: true }
  } ]
}

Applying the above query in the Query bar generates the following chart and aggregation pipeline:

Aggregation pipline example with query.

Aggregation Pipeline:

{
  "$match": {                              // Query
    "$and": [
      {
        "saleDate": {
          "$gte": {
            "$date": "2017-01-01T00:00:00Z"
          }
        }
      },
      {
        "items.4": {
          "$exists": true
        }
      }
    ]
  }
},
{
  "$addFields": {
    "__alias_0": {
      "$sum": "$items.price"
    }
 },
{
  "$group": {
    "_id": {
      "__alias_1": "$purchaseMethod"
    },
    "__alias_0": {
      "$sum": "$__alias_0"
     }
  }
},
{
  "$project": {
    "_id": 0,
    "__alias_1": "$_id.__alias_1",
    "__alias_0": 1
  }
},
{
  "$project": {
    "y": "$__alias_0",
    "x": "$__alias_1",
    "_id": 0
  }
},
{
  "$limit": 5000
}

The aggregation pipeline now starts with the query applied, and is followed by the groups selected in the Encode panel and the max document limit.

Adding Calculated Fields

We can also change the chart to show the total revenue generated categorized by purchase method. To accomplish this task, we will create a calculated field that calculates the total revenue by multiplying price by quantity. Adding this new calculated field, in addition to the query above, produces the following chart and pipeline:

Calculated Field Expression:

Aggregation pipline example with calculated field.

Aggregation Pipeline:

{
  "$match": {
    "$and": [
      {
        "saleDate": {
          "$gte": {
            "$date": "2017-01-01T00:00:00Z"
          }
        }
      },
      {
        "items.4": {
          "$exists": true
        }
      }
    ]
  }
},
{
  "$addFields": {                          // Calculated Field
    "revenue": {
      "$reduce": {
        "input": "$items",
        "initialValue": 0,
        "in": {
          "$sum": [
            "$$value",
            {
              "$multiply": [
                "$$this.price",
                "$$this.quantity"
              ]
            }
          ]
        }
      }
    }
  }
},
{
  "$group": {
    "_id": {
      "__alias_0": "$purchaseMethod"
    },
    "__alias_1": {
      "$sum": "$revenue"
    }
  }
},
{
  "$project": {
    "_id": 0,
    "__alias_0": "$_id.__alias_0",
    "__alias_1": 1
  }
},
{
  "$project": {
    "x": "$__alias_0",
    "y": "$__alias_1",
    "_id": 0
  }
},
{
  "$limit": 5000
}

The updated pipeline now includes the calculated field right below the query applied in the Query bar while the order of the rest of the components remains unchanged.

Adding Filters

This chart can be further refined by adding a filter in the Filter pane to only select in-store sales made in the New York location. Adding this filter produces the following chart and aggregation pipeline:

Aggregation pipline example with filter.

Aggregation Pipeline:

{
  "$match": {
    "$and": [
      {
        "saleDate": {
          "$gte": {
            "$date": "2017-01-01T00:00:00Z"
          }
        }
      },
      {
        "items.4": {
          "$exists": true
        }
      }
    ]
  }
},
{
  "$addFields": {
    "revenue": {
      "$reduce": {
        "input": "$items",
        "initialValue": 0,
        "in": {
          "$sum": [
            "$$value",
            {
              "$multiply": [
                "$$this.price",
                "$$this.quantity"
              ]
            }
          ]
        }
      }
    }
  }
},
{
  "$match": {                              // Filter
    "storeLocation": {
      "$nin": [
        null,
        "",
        "Austin",
        "Denver",
        "London",
        "San Diego",
        "Seattle"
      ]
    }
  }
},
{
  "$group": {
    "_id": {
      "__alias_0": "$purchaseMethod"
    },
    "__alias_1": {
      "$sum": {
        "$cond": [
          {
            "$ne": [
              {
                "$type": "$Revenue "
              },
              "missing"
            ]
          },
          1,
          0
        ]
      }
    }
  }
},
{
  "$project": {
    "_id": 0,
    "__alias_0": "$_id.__alias_0",
    "__alias_1": 1
  }
},
{
  "$project": {
    "x": "$__alias_0",
    "y": "$__alias_1",
    "_id": 0
  }
},
{
  "$limit": 5000
}

The pipeline now includes the storeLocation filter right below the calculated field while the order of the rest of the components remains unchanged.

←   Sample Mode Chart Types  →