MongoDB Aggregations | Avenue Help Center

How to Query MongoDB Aggregations Pipeline

MongoDB Aggregation Pipelines are a robust way to process data by transforming and summarizing it. In Avenue, when you select a MongoDB data source, an option to enable aggregations appears. This feature allows you to execute complex queries, extracting valuable insights from your data.

Crafting Your First Query

Creating your first aggregation query might seem daunting, but it's quite straightforward. Start by defining the collection you want to query. Next, use the `$match` operator to filter your data based on specific criteria. Then, introduce a `$group` stage to aggregate data as per your needs, like summing up quantities or counting items. Finally, use `$project` to shape the output, selecting only the fields you require. Please see MongoDB's Aggregation Pipeline documentation for more information.

Here are a few example queries that you could use in Avenue:

Example 1: Aggregating Sales Data

This query is designed to aggregate sales data from a sales collection. It filters records for a specific year, groups them by product, and calculates the total sales and average price for each product.

{
  "collection": "sales",
  "aggregate": [
    {
      "$match": {
        "saleDate": {
          "$gte": "2023-01-01",
          "$lte": "2023-12-31"
        }
      }
    },
    {
      "$group": {
        "_id": "$productId",
        "totalSales": {
          "$sum": "$amount"
        },
        "averagePrice": {
          "$avg": "$price"
        }
      }
    },
    {
      "$project": {
        "productId": "$_id",
        "_id": 0,
        "totalSales": 1,
        "averagePrice": 1
      }
    }
  ]
}

Here's a quick breakdown on each part of the query:

"collection": "sales": Specifies the collection to query, in this case, a collection named 'sales'.

"$match": {...}: Filters the documents to pass only those that match the given condition.
- "saleDate": {"$gte": "2023-01-01", "$lte": "2023-12-31"}: Filters records with sale dates within the year 2023.
"$group": {...}: Groups the documents by some specified expression.
- "_id": "$productId": Groups the data by 'productId'.
- "totalSales": {"$sum": "$amount"}: Sums the 'amount' field for each group, representing total sales.
- "averagePrice": {"$avg": "$price"}: Calculates the average 'price' for each group.
"$project": {...}: Controls the output to include/exclude fields.
- "productId": "$_id", "_id": 0: Renames the '_id' field to 'productId' and excludes the original '_id' field.
- "totalSales": 1, "averagePrice": 1: Includes the 'totalSales' and 'averagePrice' fields in the output.

Example 2: Employee Performance Analysis

In this example, the query aggregates data from an employee_performance collection. It filters records for a specific quarter, groups them by department, and calculates the average performance score for each department.

{
  "collection": "employee_performance",
  "aggregate": [
    {
      "$match": {
        "quarter": "Q1-2023"
      }
    },
    {
      "$group": {
        "_id": "$departmentId",
        "averageScore": {
          "$avg": "$performanceScore"
        }
      }
    },
    {
      "$project": {
        "departmentId": "$_id",
        "_id": 0,
        "averageScore": 1
      }
    }
  ]
}

Here's a quick breakdown on each part of the query:

"collection": "employee_performance": Targets the 'employee_performance' collection for the query.
"$match": {...}: Filters documents according to the specified criteria.
- "quarter": "Q1-2023": Selects records from the first quarter of 2023.
"$group": {...}: Groups documents by a specified identifier.
- "_id": "$departmentId": Groups the data by 'departmentId'.
- "averageScore": {"$avg": "$performanceScore"}: Computes the average 'performanceScore' for each department.
"$project": {...}: Specifies the structure of the output documents.
- "departmentId": "$_id", "_id": 0: Sets 'departmentId' as the new field name for the group identifier and removes the original '_id' field.
- "averageScore": 1: Includes the 'averageScore' field in the final output.

These examples provide a basic framework for constructing MongoDB Aggregation Pipeline queries in Avenue. They illustrate the use of $match for filtering data, $group for aggregating, and $project for shaping the final output. The queries can be further customized based on specific business requirements and data structures.

Handlebars