MongoDB Query to Pivot Data with Aggregation Pipeline

Use MongoDB's aggregation pipeline with $group to group data by a key and $project to reshape fields, effectively pivoting data; for example, db.collection.aggregate([{ $group: { _id: "$category", total: { $sum: "$amount" } } }]).

Examples

Input[{ category: "A", amount: 10 }, { category: "B", amount: 20 }, { category: "A", amount: 15 }]

Output[{ _id: "A", total: 25 }, { _id: "B", total: 20 }]

Input[{ product: "Pen", month: "Jan", sales: 100 }, { product: "Pen", month: "Feb", sales: 150 }, { product: "Pencil", month: "Jan", sales: 200 }]

Output[{ _id: "Pen", Jan: 100, Feb: 150 }, { _id: "Pencil", Jan: 200 }]

Input[]

Output[]

How to Think About It

To pivot data in MongoDB, think of grouping documents by the pivot key using $group, then reshape the grouped data into columns using $project or $arrayToObject. This transforms rows into columns by aggregating values under new field names.

Algorithm

1

Identify the field to group by (pivot key).

2

Use <code>$group</code> to aggregate data by this key.

3

Create new fields for each pivot column using aggregation expressions.

4

Use <code>$project</code> to format the output document with pivoted columns.

Code

mongodb

db.sales.aggregate([
  {
    $group: {
      _id: "$product",
      Jan: { $sum: { $cond: [{ $eq: ["$month", "Jan"] }, "$sales", 0] } },
      Feb: { $sum: { $cond: [{ $eq: ["$month", "Feb"] }, "$sales", 0] } }
    }
  }
])

Output

[
  { "_id": "Pen", "Jan": 100, "Feb": 150 },
  { "_id": "Pencil", "Jan": 200 }
]

Dry Run

Let's trace the pivot of sales data by product and month through the aggregation.

1

Group by product

Group documents by product: 'Pen' and 'Pencil'.

2

Sum sales conditionally

For each product, sum sales where month is 'Jan' and separately where month is 'Feb'.

3

Output pivoted fields

Create fields 'Jan' and 'Feb' with summed sales values.

Product	Jan Sales Sum	Feb Sales Sum
Pen	100	150
Pencil	200	0

Why This Works

Step 1: Grouping data

The $group stage collects documents by the pivot key, here product, to aggregate related data.

Step 2: Conditional aggregation

Using $cond inside $sum lets us sum sales only for specific months, creating separate columns.

Step 3: Pivot output

The result documents have fields named after months, showing sales per product per month, effectively pivoting rows into columns.

Alternative Approaches

Using $facet for multiple pivots

mongodb

db.sales.aggregate([
  {
    $facet: {
      Jan: [ { $match: { month: "Jan" } }, { $group: { _id: "$product", total: { $sum: "$sales" } } } ],
      Feb: [ { $match: { month: "Feb" } }, { $group: { _id: "$product", total: { $sum: "$sales" } } } ]
    }
  }
])

This separates aggregation by month but requires additional processing to merge results; useful for complex pivots.

Using $arrayToObject and $map

mongodb

db.sales.aggregate([
  {
    $group: {
      _id: "$product",
      salesByMonth: { $push: { k: "$month", v: "$sales" } }
    }
  },
  {
    $project: {
      salesByMonth: { $arrayToObject: "$salesByMonth" }
    }
  }
])

This creates a dynamic object with months as keys, good for unknown or many pivot columns.

Complexity: O(n) time, O(k) space

Time Complexity

The aggregation scans all documents once, so time is linear in the number of documents, O(n).

Space Complexity

Space depends on the number of groups (k) created by $group, storing aggregated results.

Which Approach is Fastest?

Using $group with $cond is efficient for known pivot keys; $facet adds overhead but helps complex pivots.

Approach	Time	Space	Best For
$group with $cond	O(n)	O(k)	Known fixed pivot columns
$facet with multiple $group	O(n)	O(k)	Complex or multiple pivots
$arrayToObject with dynamic keys	O(n)	O(k)	Dynamic or unknown pivot columns