Free tool

MongoDB aggregation pipeline generator

Describe the report you need, revenue by month, top products, a funnel, a join, and get a MongoDB aggregation pipeline with every stage explained in plain English. Pipelines are checked against an allowlist of read-only stages.

  • No signup
  • Gemini writes it, the read-only guard checks it
  • 13 verified examples
0/2000·orders

Describe the report you need. Press ⌘ + Enter to run.

552/20,000

JSON or mongosh syntax. ObjectId(...) and ISODate(...) are fine. Replace real values; never paste credentials.

13 worked examples,
run against real data

Every example ran on the sample orders, customers, tickets, events, products collections before it was published, and the table under it is what MongoDB returned. Load any of them into the tool above.

Worked examples
  • aggregateorders01

    Revenue and order count by status

    “Total revenue and number of orders for each status, highest revenue first”

    db.orders.aggregate([
      {
        $group: { _id: "$status", orders: { $sum: 1 }, revenue: { $sum: "$total" } }
      },
      { $sort: { revenue: -1 } }
    ])
    • $group with _id: "$status" makes one output document per status.
    • { $sum: 1 } counts documents; { $sum: "$total" } adds up the order totals.
    Result of Revenue and order count by status on the sample data
    _idordersrevenue
    shipped137998
    pending132496
    delivered222096
    cancelled11299
  • aggregateorders02

    Monthly revenue for a year

    “Revenue per month in 2026, excluding cancelled orders”

    db.orders.aggregate([
      {
        $match: {
          status: { $ne: "cancelled" },
          createdAt: {
            $gte: ISODate("2026-01-01T00:00:00Z"),
            $lt: ISODate("2027-01-01T00:00:00Z")
          }
        }
      },
      {
        $group: {
          _id: { $dateToString: { format: "%Y-%m", date: "$createdAt" } },
          revenue: { $sum: "$total" },
          orders: { $sum: 1 }
        }
      },
      { $sort: { _id: 1 } }
    ])
    • $match first, so only 2026 orders that were not cancelled reach the group (and an index can help).
    • $dateToString with %Y-%m turns each timestamp into a month key like 2026-09.
    Result of Monthly revenue for a year on the sample data
    _idrevenueorders
    2026-08129991
    2026-09470952
    2026-10324961
  • aggregateorders03

    Top 3 products by units sold

    “Top 3 products by units sold, with revenue, ignoring cancelled orders”

    db.orders.aggregate([
      { $match: { status: { $ne: "cancelled" } } },
      { $unwind: "$items" },
      {
        $group: {
          _id: "$items.sku",
          name: { $first: "$items.name" },
          units: { $sum: "$items.qty" },
          revenue: { $sum: { $multiply: ["$items.qty", "$items.price"] } }
        }
      },
      { $sort: { units: -1, _id: 1 } },
      { $limit: 3 }
    ])
    • $unwind turns each order into one document per line item.
    • $group by SKU adds up quantities and quantity times price.
    Result of Top 3 products by units sold on the sample data
    _idunitsrevenue
    KB-201425996
    HP-330225998
    MN-270237998
  • aggregateorders04

    Average order value by payment method

    “Average order value for each payment method, rounded, excluding cancelled orders”

    db.orders.aggregate([
      { $match: { status: { $ne: "cancelled" } } },
      {
        $group: {
          _id: "$paymentMethod",
          orders: { $sum: 1 },
          avgOrderValue: { $avg: "$total" }
        }
      },
      {
        $project: { orders: 1, avgOrderValue: { $round: ["$avgOrderValue", 0] } }
      },
      { $sort: { avgOrderValue: -1 } }
    ])
    • $avg inside $group computes the mean total per payment method.
    • $round runs in a later $project because accumulators cannot be wrapped directly.
    Result of Average order value by payment method on the sample data
    _idordersavgOrderValue
    card327831
    upi19097
  • aggregatecustomers05

    Spend per customer (join)

    “Each customer's number of orders and total spend, excluding cancelled orders”

    db.customers.aggregate([
      {
        $lookup: {
          from: "orders",
          localField: "_id",
          foreignField: "customerId",
          pipeline: [{ $match: { status: { $ne: "cancelled" } } }],
          as: "orders"
        }
      },
      {
        $project: {
          _id: 0,
          name: 1,
          orders: { $size: "$orders" },
          spend: { $sum: "$orders.total" }
        }
      },
      { $sort: { spend: -1, name: 1 } }
    ])
    • $lookup joins each customer to their orders; the inner pipeline drops cancelled ones.
    • $size counts the joined orders and $sum over "$orders.total" adds their totals.
    Result of Spend per customer (join) on the sample data
    nameordersspend
    Priya Nair270494
    Sofia Rossi112999
    Aarav Mehta19097
    Daniel Okafor00

    + 1 more rows

  • aggregatetickets06

    Most common ticket tags

    “The 5 most common ticket tags”

    db.tickets.aggregate([
      { $unwind: "$tags" },
      { $group: { _id: "$tags", tickets: { $sum: 1 } } },
      { $sort: { tickets: -1, _id: 1 } },
      { $limit: 5 }
    ])
    • $unwind on tags emits one document per tag, so a ticket with two tags counts for both.
    • Grouping by the tag and counting gives the frequency of each one.
    Result of Most common ticket tags on the sample data
    _idtickets
    orders2
    account1
    billing1
    hardware1

    + 1 more rows

  • aggregatetickets07

    First response time by channel

    “Average first response time in hours for each support channel”

    db.tickets.aggregate([
      { $match: { firstResponseAt: { $ne: null } } },
      {
        $group: {
          _id: "$channel",
          tickets: { $sum: 1 },
          avgMinutes: {
            $avg: {
              $dateDiff: { startDate: "$createdAt", endDate: "$firstResponseAt", unit: "minute" }
            }
          }
        }
      },
      {
        $project: {
          tickets: 1,
          avgHours: { $round: [{ $divide: ["$avgMinutes", 60] }, 1] }
        }
      },
      { $sort: { avgHours: -1 } }
    ])
    • Tickets without a first response are filtered out so they don't skew the average.
    • $dateDiff measures the gap in minutes; dividing by 60 and rounding gives hours.
    Result of First response time by channel on the sample data
    _idticketsavgHours
    email29.3
    chat20.3
  • aggregateevents08

    Daily active users in a month

    “Daily active users and event counts for October 2026”

    db.events.aggregate([
      {
        $match: {
          ts: {
            $gte: ISODate("2026-10-01T00:00:00Z"),
            $lt: ISODate("2026-11-01T00:00:00Z")
          }
        }
      },
      {
        $group: {
          _id: { $dateToString: { format: "%Y-%m-%d", date: "$ts" } },
          users: { $addToSet: "$userId" },
          events: { $sum: 1 }
        }
      },
      {
        $project: { _id: 0, day: "$_id", activeUsers: { $size: "$users" }, events: 1 }
      },
      { $sort: { day: 1 } }
    ])
    • $addToSet collects each user once per day, so repeat events don't inflate the count.
    • $size of that set is the number of distinct active users.
    Result of Daily active users in a month on the sample data
    dayactiveUsersevents
    2026-10-0113
    2026-10-1311
  • aggregateevents09

    Conversion funnel by unique users

    “How many unique users viewed a page, added to cart and purchased?”

    db.events.aggregate([
      { $match: { event: { $in: ["page_view", "add_to_cart", "purchase"] } } },
      { $group: { _id: "$event", users: { $addToSet: "$userId" } } },
      { $project: { _id: 0, step: "$_id", users: { $size: "$users" } } },
      { $sort: { users: -1, step: 1 } }
    ])
    • Only the three funnel events are kept.
    • Counting distinct userIds per event gives users per step, not raw event volume.
    Result of Conversion funnel by unique users on the sample data
    stepusers
    page_view2
    add_to_cart1
    purchase1
  • aggregateproducts10

    Inventory value per category

    “Stock value (price times stock) for each category of active products”

    db.products.aggregate([
      { $match: { active: true } },
      {
        $group: {
          _id: "$category",
          skus: { $sum: 1 },
          stockValue: { $sum: { $multiply: ["$price", "$stock"] } }
        }
      },
      { $sort: { stockValue: -1 } }
    ])
    • $multiply computes price times stock for every product before $sum adds them up.
    • Inactive products are excluded first.
    Result of Inventory value per category on the sample data
    _idskusstockValue
    accessories2272958
    audio1194985
    displays1132993
  • aggregateproducts11

    Stock by variant colour

    “Total stock for each colour across all product variants”

    db.products.aggregate([
      { $unwind: "$variants" },
      {
        $group: { _id: "$variants.color", stock: { $sum: "$variants.stock" } }
      },
      { $sort: { stock: -1 } }
    ])
    • $unwind on variants gives one document per colour variant; products with no variants drop out.
    • Grouping by the colour sums stock across every product.
    Result of Stock by variant colour on the sample data
    _idstock
    black39
    white12
    silver6
  • aggregateproducts12

    Products per price band

    “How many products fall into each price band: under 1,000, 1,000 to 5,000, 5,000 to 15,000 and above”

    db.products.aggregate([
      {
        $bucket: {
          groupBy: "$price",
          boundaries: [0, 1000, 5000, 15000, 50000],
          default: "other",
          output: { products: { $sum: 1 }, skus: { $push: "$sku" } }
        }
      }
    ])
    • $bucket assigns each product to a band; each _id is the band's lower bound.
    • Prices outside every band would land in the "other" bucket instead of failing.
    Result of Products per price band on the sample data
    _idproductsskus
    01CB-050
    10001MS-110
    50002KB-201, HP-330
    150001MN-270
  • aggregatecustomers13

    New signups who haven't ordered

    “Customers who signed up in the last 30 days and have never placed an order”

    db.customers.aggregate([
      {
        $match: {
          $expr: {
            $gte: [
              "$signupDate",
              { $dateSubtract: { startDate: "$$NOW", unit: "day", amount: 30 } }
            ]
          }
        }
      },
      {
        $lookup: {
          from: "orders",
          localField: "_id",
          foreignField: "customerId",
          as: "orders"
        }
      },
      { $match: { orders: { $size: 0 } } },
      { $project: { _id: 0, name: 1, email: 1, signupDate: 1 } }
    ])
    • The first $match uses $$NOW, so "last 30 days" is relative to when the query runs.
    • $lookup attaches orders and { $size: 0 } keeps customers with none: an anti-join.

    Result depends on when it runs ($$NOW or $sample), so no fixed preview.

How the tool works

Four steps, and none of them touch your database.

  1. 1

    Ask a reporting question

    Totals, averages, rankings, time series, joins: say what the result should look like.

  2. 2

    Paste a sample document

    Real field names and types make the difference between a pipeline that runs and one that returns nothing.

  3. 3

    Read the stages

    Each stage comes with a one-line explanation, plus assumptions and performance warnings.

  4. 4

    Run it yourself

    Copy it for mongosh, Node.js or Python. The tool never touches your data.

What it won't do

Honest limits, so you know what to expect.

  • Run the pipeline. It writes the pipeline only.
  • Use $out or $merge, or any stage outside the read-only allowlist.
  • Use server-side JavaScript ($function, $accumulator, $where).
  • Guess silently. Unknown fields and units are listed as assumptions.

The same guard as the product

Generated queries pass the check wuup runs on every connected database: only read stages, no server-side JavaScript, at most 30 stages.

Writes ($out, $merge)
Refused
Server JS ($where, $function)
Refused
Stages
Read-only allowlist
Your database
Never contacted
Inputs
Cached 7 days by hash

The concepts
behind the output

A short explanation of what the tool produces and the mistakes it is built to avoid.

How an aggregation pipeline works

A pipeline is a list of stages. Each stage takes the documents produced by the previous one, transforms them and passes the result on. $match filters, $group summarises, $project reshapes, $sort orders and $lookup joins another collection.

Order matters. Putting $match first means fewer documents reach the expensive stages, and a $match or $sort at the start of a pipeline can use an index.

Patterns worth knowing

Unwind then group to aggregate inside arrays. Group by $dateToString or $dateTrunc for time series. Use $addToSet then $size for distinct counts. Use $lookup with a nested pipeline to filter what you join. Use $facet to compute several summaries in one round trip.

Every example below is run against sample data before it is published, and its result preview shows what MongoDB actually returned.

Keep going

Guides that go deeper, the product that runs these queries for you, and the other free tools.

Questions,
answered

Stop writing queries.
Start asking wuup.