Database Ops · 6 of 6

aggregate

Run a full aggregation pipeline inside a procedure. Returns an array of documents.

Signature

aggregate(collection, [stage1, stage2, ...])

Sum

proc total_sales() {
    return aggregate("orders", [
        {$group: {_id: null, total: {$sum: "$amount"}}}
    ])
}

Group by category

proc sales_by_category() {
    return aggregate("orders", [
        {$group: {_id: "$category", total: {$sum: "$amount"}, n: {$sum: 1}}},
        {$sort: {total: -1}}
    ])
}

Filter then group

proc monthly_revenue(year, month) {
    return aggregate("orders", [
        {$match: {year: year, month: month, status: "paid"}},
        {$group: {_id: "$category", total: {$sum: "$amount"}}},
        {$sort: {total: -1}}
    ])
}

Top-N

proc top_customers(n) {
    return aggregate("orders", [
        {$group: {_id: "$customer_id", spend: {$sum: "$amount"}, orders: {$sum: 1}}},
        {$sort: {spend: -1}},
        {$limit: n}
    ])
}

Avg / min / max

proc product_stats(sku) {
    return aggregate("reviews", [
        {$match: {sku: sku}},
        {$group: {
            _id: "$sku",
            avg_rating: {$avg: "$rating"},
            min_rating: {$min: "$rating"},
            max_rating: {$max: "$rating"},
            count: {$sum: 1}
        }}
    ])
}

$lookup (join)

proc orders_with_customer() {
    return aggregate("orders", [
        {$lookup: {
            from: "customers", localField: "customer_id",
            foreignField: "_id", as: "customer"
        }},
        {$limit: 100}
    ])
}

$unwind + $group

proc tag_popularity() {
    return aggregate("posts", [
        {$unwind: "$tags"},
        {$group: {_id: "$tags", n: {$sum: 1}}},
        {$sort: {n: -1}},
        {$limit: 20}
    ])
}

Use the result

proc check_quota(user_id, monthly_limit) {
    let result = aggregate("messages", [
        {$match: {user_id: user_id, this_month: true}},
        {$group: {_id: null, total: {$sum: 1}}}
    ])
    let used = 0
    if result[0] != null {
        used = result[0].total
    }
    return {used: used, limit: monthly_limit, ok: used < monthly_limit}
}

For the full stage and operator reference, see Aggregation Pipeline.