Build Aggregation Pipelines from CLI

Intermediate15 min

Construct and test MongoDB aggregation pipelines in mongosh for data transformation, grouping, and analysis directly from the command line.

Prerequisites

-MongoDB 6+ with mongosh
-A collection with sample data

Steps

Basic match and group pipeline

Filter documents and group them to compute aggregated values.

$ mongosh --eval 'db.orders.aggregate([{$match: {status: "completed"}}, {$group: {_id: "$customerId", totalSpent: {$sum: "$amount"}, orderCount: {$sum: 1}}}, {$sort: {totalSpent: -1}}, {$limit: 10}])' mydb

Use $lookup for collection joins

Join data from another collection similar to a SQL LEFT JOIN.

$ mongosh --eval 'db.orders.aggregate([{$lookup: {from: "customers", localField: "customerId", foreignField: "_id", as: "customer"}}, {$unwind: "$customer"}, {$project: {orderId: 1, amount: 1, customerName: "$customer.name"}}, {$limit: 5}])' mydb

Date-based aggregation with $group

Group orders by month to analyze trends over time.

$ mongosh --eval 'db.orders.aggregate([{$group: {_id: {year: {$year: "$createdAt"}, month: {$month: "$createdAt"}}, revenue: {$sum: "$amount"}, count: {$sum: 1}}}, {$sort: {"_id.year": 1, "_id.month": 1}}])' mydb

Use $facet for multiple aggregations in one query

Run several aggregation pipelines in parallel within a single query.

$ mongosh --eval 'db.orders.aggregate([{$facet: {byStatus: [{$group: {_id: "$status", count: {$sum: 1}}}], topCustomers: [{$group: {_id: "$customerId", total: {$sum: "$amount"}}}, {$sort: {total: -1}}, {$limit: 5}], dailyRevenue: [{$group: {_id: {$dateToString: {format: "%Y-%m-%d", date: "$createdAt"}}, revenue: {$sum: "$amount"}}}, {$sort: {_id: -1}}, {$limit: 7}]}}])' mydb

$facet runs all sub-pipelines on the same input dataset, making it efficient for dashboard-style queries.

Explain an aggregation pipeline

Analyze the execution plan to understand how MongoDB processes the pipeline.

$ mongosh --eval 'db.orders.explain("executionStats").aggregate([{$match: {status: "completed"}}, {$group: {_id: "$customerId", total: {$sum: "$amount"}}}])' mydb

Full Script

full-script.sh

#!/bin/bash
# MongoDB Aggregation Pipeline Examples
set -euo pipefail

DB="mydb"

echo "=== Revenue by status ==="
mongosh --quiet --eval "
db.orders.aggregate([
  {$group: {
    _id: '$status',
    count: {$sum: 1},
    totalRevenue: {$sum: '$amount'},
    avgOrder: {$avg: '$amount'}
  }},
  {$sort: {totalRevenue: -1}}
]).forEach(doc => {
  print(doc._id + ': ' + doc.count + ' orders, $' + doc.totalRevenue.toFixed(2) + ' total, $' + doc.avgOrder.toFixed(2) + ' avg');
});
" "$DB"

echo ""
echo "=== Top 10 customers by spending ==="
mongosh --quiet --eval "
db.orders.aggregate([
  {$match: {status: 'completed'}},
  {$group: {_id: '$customerId', total: {$sum: '$amount'}, orders: {$sum: 1}}},
  {$sort: {total: -1}},
  {$limit: 10}
]).forEach(doc => {
  print(doc._id + ': $' + doc.total.toFixed(2) + ' (' + doc.orders + ' orders)');
});
" "$DB"

echo ""
echo "=== Monthly revenue trend ==="
mongosh --quiet --eval "
db.orders.aggregate([
  {$match: {status: 'completed'}},
  {$group: {
    _id: {$dateToString: {format: '%Y-%m', date: '$createdAt'}},
    revenue: {$sum: '$amount'},
    orders: {$sum: 1}
  }},
  {$sort: {_id: 1}}
]).forEach(doc => {
  print(doc._id + ': $' + doc.revenue.toFixed(2) + ' (' + doc.orders + ' orders)');
});
" "$DB"

FAQ

Discussion

Loading comments...

#!/bin/bash # MongoDB Aggregation Pipeline Examples set -euo pipefail DB="mydb" echo "=== Revenue by status ===" mongosh --quiet --eval " db.orders.aggregate([ {$group: { _id: '$status', count: {$sum: 1}, totalRevenue: {$sum: '$amount'}, avgOrder: {$avg: '$amount'} }}, {$sort: {totalRevenue: -1}} ]).forEach(doc => { print(doc._id + ': ' + doc.count + ' orders, $' + doc.totalRevenue.toFixed(2) + ' total, $' + doc.avgOrder.toFixed(2) + ' avg'); }); " "$DB" echo "" echo "=== Top 10 customers by spending ===" mongosh --quiet --eval " db.orders.aggregate([ {$match: {status: 'completed'}}, {$group: {_id: '$customerId', total: {$sum: '$amount'}, orders: {$sum: 1}}}, {$sort: {total: -1}}, {$limit: 10} ]).forEach(doc => { print(doc._id + ': $' + doc.total.toFixed(2) + ' (' + doc.orders + ' orders)'); }); " "$DB" echo "" echo "=== Monthly revenue trend ===" mongosh --quiet --eval " db.orders.aggregate([ {$match: {status: 'completed'}}, {$group: { _id: {$dateToString: {format: '%Y-%m', date: '$createdAt'}}, revenue: {$sum: '$amount'}, orders: {$sum: 1} }}, {$sort: {_id: 1}} ]).forEach(doc => { print(doc._id + ': $' + doc.revenue.toFixed(2) + ' (' + doc.orders + ' orders)'); }); " "$DB"