MongoDB Aggregation Pipeline: Advanced Query Techniques

The aggregation pipeline enables powerful data processing capabilities in MongoDB, including table joins and statistical analysis. It processes documents through multiple stages, transforming data at each step.

Basic syntax:

db.COLLECTION_NAME.aggregate([STAGE_1, STAGE_2, ...])

2. Pipeline Operators and Expressions

Pipeline operators act as keys, while their corresponding values are called pipeline expressions. These expresions are document structures composed of field names, field values, and operator expressions.

For example, in {$match:{status:"active"}}, $match is the operator and status:"active" is the expression.

Operator	SQL Equivalent	Purpose
$project	SELECT	Reshapes documents by including/excluding fields, renaming fields, or computing new values
$match	WHERE/HAVING	Filters documents based on conditions; only matching documents proceed to the next stage
$limit	LIMIT	Restricts the number of documents passed to the next stage
$skip	-	Bypasses a specified number of documents
$sort	ORDER BY	Orders documents by specified fields
$group	GROUP BY	Groups input documents for aggregation computations
$lookup	JOIN	Performs left outer join with another collection

Aggregation Expressions

Expression	Description	Example
$sum	Calculates total sum of numeric values	`db.sales.aggregate([{$group:{_id:"$region",total:{$sum:"$amount"}}}]);`
$avg	Calculates average of numeric values	`db.sales.aggregate([{$group:{_id:"$region",avgAmount:{$avg:"$amount"}}}]);`
$min	Returns minimum value from documents	`db.sales.aggregate([{$group:{_id:"$region",minAmount:{$min:"$amount"}}}]);`
$max	Returns maximum value from documents	`db.sales.aggregate([{$group:{_id:"$region",maxAmount:{$max:"$amount"}}}]);`
$push	Appends values to an array in result documents	`db.sales.aggregate([{$group:{_id:"$region",items:{$push:"$product"}}}]);`
$addToSet	Adds values to an array with out duplicates	`db.sales.aggregate([{$group:{_id:"$region",products:{$addToSet:"$product"}}}]);`
$first	Retrieves first document based on sort order	`db.sales.aggregate([{$group:{_id:"$region",firstSale:{$first:"$date"}}}]);`
$last	Retrieves last document based on sort order	`db.sales.aggregate([{$group:{_id:"$region",lastSale:{$last:"$date"}}}]);`

3. Sample Data Setup

db.sales.insert({"invoice":"A001","customer_id":15,"reference":"TRX111","subtotal":150,"quantity":3})
db.sales.insert({"invoice":"A002","customer_id":8,"reference":"TRX222","subtotal":85,"quantity":2})
db.sales.insert({"invoice":"A003","customer_id":12,"reference":"TRX333","subtotal":35,"quantity":7})
db.line_items.insert({"invoice":"A001","product":"Wireless Mouse","unit_price":60,"quantity":1})
db.line_items.insert({"invoice":"A001","product":"Mechanical Keyboard","unit_price":90,"quantity":1})
db.line_items.insert({"invoice":"A001","product":"USB Hub","unit_price":0,"quantity":1})
db.line_items.insert({"invoice":"A002","product":"Coffee Beans","unit_price":50,"quantity":1})
db.line_items.insert({"invoice":"A002","product":"Coffee Filters","unit_price":35,"quantity":1})
db.line_items.insert({"invoice":"A003","product":"Bottled Water","unit_price":5,"quantity":6})
db.line_items.insert({"invoice":"A003","product":"Hand Towel","unit_price":5,"quantity":1})

4. Practical Examples

Using $project

Retireve only reference and subtotal fields from sales documents:

db.sales.aggregate([
    {
        $project: { reference: 1, subtotal: 1 }
    }
]);

Using $match

Filter sales documents where subtotal is greater than or equal to 85:

db.sales.aggregate([
    {
        $project: { reference: 1, subtotal: 1 }
    },
    {
        $match: { subtotal: { $gte: 85 } }
    }
]);

Using $group

Calculate total quantity for each invoice grouped by invoice number:

db.line_items.aggregate([
    {
        $group: { _id: "$invoice", totalQuantity: { $sum: "$quantity" } }
    }
]);

Using $sort

Sort filtered results by subtotal in descending order:

db.sales.aggregate([
    {
        $project: { reference: 1, subtotal: 1 }
    },
    {
        $match: { subtotal: { $gte: 85 } }
    },
    {
        $sort: { subtotal: -1 }
    }
]);

Using $limit

Retrieve only the top result after sorting:

db.sales.aggregate([
    {
        $project: { reference: 1, subtotal: 1 }
    },
    {
        $match: { subtotal: { $gte: 85 } }
    },
    {
        $sort: { subtotal: -1 }
    },
    {
        $limit: 1
    }
]);

Using $skip

Skip the top result and retrieve remaining documents:

db.sales.aggregate([
    {
        $project: { reference: 1, subtotal: 1 }
    },
    {
        $match: { subtotal: { $gte: 85 } }
    },
    {
        $sort: { subtotal: -1 }
    },
    {
        $skip: 1
    }
]);

Using $lookup for Collection Joins

Join sales with line items to include product details in results:

db.sales.aggregate([
    {
        $lookup: {
            from: "line_items",
            localField: "invoice",
            foreignField: "invoice",
            as: "order_details"
        }
    },
    {
        $project: { reference: 1, subtotal: 1, order_details: 1 }
    }
]);

The $lookup operator performs a left outer join, matching documents from the from collection where foreignField equals localField, and stores matched documents in the specified as array field.

Tags: mongodb aggregation-framework Pipeline NoSQL database-queries

Posted on Sat, 15 Aug 2026 16:48:01 +0000 by Skittlewidth

Freaks City