$first and $last accumulators in MongoDB - Deep Dive

Overview - $first and $last accumulators

What is it?

$first and $last are special tools in MongoDB used during data grouping. They help pick the very first or very last value from a group of items based on the order of documents. This is useful when you want to find the earliest or latest entry in a set of data. They work inside the aggregation framework, which processes data step-by-step.

Why it matters

Without $first and $last, finding the earliest or latest value in grouped data would be complicated and slow. These accumulators make it easy to get meaningful insights like the first sale date or last login time from large datasets. They save time and reduce errors compared to manual sorting and filtering.

Where it fits

Before learning $first and $last, you should understand MongoDB basics and the aggregation framework, especially the $group stage. After mastering these accumulators, you can explore more complex aggregation operators and pipeline stages like $sort, $project, and $lookup to build powerful data queries.

Mental Model

Core Idea

$first and $last pick the earliest and latest values from ordered groups of documents during aggregation.

Think of it like...

Imagine a line of people waiting to enter a concert. $first is like the person who entered first, and $last is the person who entered last. You only care about these two people from the whole line.

Group of documents ──────────────▶ Ordered by some field
  │                                   │
  │                                   ├─> $first picks the first document's value
  │                                   └─> $last picks the last document's value
  ▼
Aggregation $group stage

Build-Up - 7 Steps

1

Foundation

Understanding MongoDB Aggregation Basics

Concept: Learn what aggregation is and how it groups data.

Aggregation in MongoDB is like a factory line where data is processed step-by-step. The $group stage collects documents into groups based on a key, like grouping all sales by product. This sets the stage for using accumulators like $first and $last to pick values from each group.

Result

You can group documents by a field and prepare to summarize or extract data from each group.

Understanding grouping is essential because $first and $last only work inside groups, so knowing how groups form is the foundation.

2

Foundation

What Are Accumulators in Aggregation?

Concept: Accumulators calculate values from grouped documents.

Accumulators are functions that take all documents in a group and return a single value, like sum, average, or count. $first and $last are special accumulators that return the first or last document's value in the group, based on the current document order.

Result

You know that accumulators summarize or pick values from groups, enabling meaningful data extraction.

Recognizing accumulators as summarizers helps you see $first and $last as tools to pick specific values, not just numbers.

3

Intermediate

How $first and $last Work with Document Order

🤔Before reading on: Do you think $first and $last automatically sort documents before picking values? Commit to yes or no.

Concept: $first and $last pick values based on the current order of documents, not automatic sorting.

$first returns the value from the first document in the group as MongoDB sees it, and $last returns from the last document. MongoDB does NOT sort documents automatically before applying these accumulators. To control which document is first or last, you must sort the data explicitly before grouping.

Result

Without sorting, $first and $last may return unexpected values because document order is not guaranteed.

Knowing that $first and $last depend on document order prevents mistakes where results seem random; sorting before grouping is key.

4

Intermediate

Using $sort Before $group to Control $first and $last

🤔Before reading on: If you want the earliest date with $first, should you sort ascending or descending? Commit to your answer.

Concept: Sorting documents before grouping sets the order for $first and $last to pick correct values.

To get meaningful $first or $last results, use a $sort stage before $group. For example, sorting by date ascending means $first picks the earliest date, and $last picks the latest. Sorting descending reverses this. This explicit control ensures $first and $last return the values you expect.

Result

You get accurate first or last values based on your sorting criteria.

Understanding the need for sorting before grouping empowers you to use $first and $last reliably in real queries.

5

Intermediate

Practical Example: Finding First and Last Sales

Concept: Apply $first and $last to find earliest and latest sales per product.

Suppose you have sales data with product IDs and sale dates. You can sort by product and date ascending, then group by product ID. Using $first on the sale date gives the earliest sale, and $last gives the latest sale for each product.

Result

A list of products with their first and last sale dates.

Seeing $first and $last in action clarifies their practical use and how sorting shapes results.

6

Advanced

Combining $first and $last with Other Accumulators

🤔Before reading on: Can $first and $last be used alongside $sum or $avg in the same $group? Commit to yes or no.

Concept: You can mix $first and $last with other accumulators to get rich summaries.

In a single $group stage, you might want the first and last values plus totals or averages. For example, get the first sale date, last sale date, and total sales count per product. MongoDB allows combining these accumulators, making aggregation flexible and powerful.

Result

A grouped summary with multiple insights per group.

Knowing you can combine accumulators lets you build complex reports in one aggregation pipeline.

7

Expert

Surprising Behavior: $first and $last Without $sort

🤔Before reading on: Do you think $first and $last always return consistent values without sorting? Commit to yes or no.

Concept: $first and $last depend on document order, which is not guaranteed without sorting, leading to unpredictable results.

If you skip the $sort stage, MongoDB processes documents in an undefined order. This means $first and $last might pick different documents each time you run the query. This behavior can cause bugs if you assume stable results. Always sort explicitly to avoid surprises.

Result

Unstable or inconsistent first and last values when sorting is missing.

Understanding this subtlety prevents hard-to-find bugs in production systems using $first and $last.

Under the Hood

$first and $last work during the $group stage of aggregation. MongoDB processes documents in the order they arrive at $group. $first stores the value from the first document it sees in each group, and $last updates its stored value with each new document, ending with the last one. Without prior sorting, the document order is arbitrary, so these accumulators reflect that order.

Why designed this way?

MongoDB designed $first and $last to be simple and efficient by relying on document order rather than sorting internally. Sorting can be expensive, so separating sorting ($sort stage) from grouping ($group stage) gives developers control and better performance. This design follows the pipeline model where each stage does one job well.

Documents Stream ──▶ [$sort stage] ──▶ Ordered Documents ──▶ [$group stage]
                                         │                      │
                                         │                      ├─> $first picks first document's value
                                         │                      └─> $last picks last document's value
                                         ▼                      
                                  Grouped Results

Myth Busters - 4 Common Misconceptions

Quick: Does $first automatically find the earliest date in a group without sorting? Commit to yes or no.

Common Belief: $first always returns the earliest or smallest value in a group automatically.

Reality: $first returns the value from the first document MongoDB processes in the group, which depends on document order, not value sorting.

Why it matters: Assuming $first sorts internally leads to wrong results and bugs when the data order is not controlled.

Quick: Can $last be used without a $sort stage and still reliably get the latest value? Commit to yes or no.

Common Belief: $last always returns the latest or largest value in a group without needing sorting.

Reality: $last returns the value from the last document processed, which is unpredictable without prior sorting.

Why it matters: Ignoring the need to sort before grouping causes inconsistent and unreliable outputs.

Quick: Is it possible to use $first and $last outside of $group? Commit to yes or no.

Common Belief: $first and $last can be used anywhere in aggregation pipelines.

Reality: $first and $last are accumulators that only work inside the $group stage.

Why it matters: Trying to use them elsewhere causes errors and confusion about their purpose.

Quick: Do $first and $last always return the same document fields? Commit to yes or no.

Common Belief: $first and $last always return the entire document from the group.

Reality: $first and $last return the value of a specific field you specify, not the whole document.

Why it matters: Misunderstanding this leads to incorrect query results or errors when expecting full documents.

Expert Zone

1

$first and $last reflect the order of documents as they arrive at $group, which can be influenced by indexes, sharding, or pipeline stages, making their behavior subtle in distributed setups.

2

Using $first and $last with $push or $addToSet can help capture both boundary values and full lists, enabling richer data summaries.

3

In sharded clusters, $first and $last operate per shard before merging, so results depend on shard ordering and require careful pipeline design.

When NOT to use

Avoid $first and $last when you need guaranteed min or max values regardless of document order; use $min and $max instead. Also, if you want full documents rather than field values, consider $top and $bottom accumulators introduced in newer MongoDB versions.

Production Patterns

In production, $first and $last are often used after sorting to find first/last events like user signups or transactions. They appear in reports, dashboards, and audit logs. Combining them with $sort and $limit stages helps build efficient queries that scale well.

Connections

SQL Window Functions

$first and $last are similar to SQL's FIRST_VALUE() and LAST_VALUE() window functions that pick boundary values in ordered partitions.

Knowing SQL window functions helps understand how MongoDB picks first and last values within groups, bridging NoSQL and SQL concepts.

Event Stream Processing

Both $first/$last accumulators and event stream processors track first and last events in ordered streams.

Understanding event streams clarifies why ordering matters and how picking first/last events is a common pattern in data processing.

Human Memory Recall

Just as people tend to remember the first and last items in a list better (serial position effect), $first and $last pick boundary values in data groups.

This connection shows how natural patterns of attention and memory reflect in data aggregation techniques.

Common Pitfalls

#1 Getting unexpected $first or $last values without sorting.

Wrong approach:

db.sales.aggregate([{ $group: { _id: "$product", firstSale: { $first: "$date" } } }])

Correct approach:

db.sales.aggregate([{ $sort: { date: 1 } }, { $group: { _id: "$product", firstSale: { $first: "$date" } } }])

Root cause:

Assuming $first sorts documents internally, ignoring the need for explicit $sort.

#2 Using $first or $last outside $group stage.

Wrong approach:

db.collection.aggregate([{ $project: { firstValue: { $first: "$field" } } }])

Correct approach:

db.collection.aggregate([{ $group: { _id: null, firstValue: { $first: "$field" } } }])

Root cause:

Misunderstanding that $first and $last are accumulators only valid inside $group.

#3 Expecting $first and $last to return full documents.

Wrong approach:

db.collection.aggregate([{ $group: { _id: "$category", firstDoc: { $first: "$$ROOT" } } }])

Correct approach:

db.collection.aggregate([{ $sort: { date: 1 } }, { $group: { _id: "$category", firstDoc: { $first: "$$ROOT" } } }])

Root cause:

Not sorting before grouping when using $$ROOT to get full documents, leading to unpredictable results.

Key Takeaways

$first and $last accumulators pick values from the first and last documents in each group based on document order.

Document order is not guaranteed unless you explicitly sort before grouping, so always use $sort to control $first and $last results.

These accumulators only work inside the $group stage and return specific field values, not entire documents unless using $$ROOT carefully.

Combining $first and $last with other accumulators allows rich data summaries in a single aggregation pipeline.

Understanding the internal behavior of $first and $last prevents bugs and helps build reliable, efficient MongoDB queries.

Practice

1. What does the $first accumulator do in a MongoDB aggregation pipeline?

easy

A. It returns the last value of a specified field in each group.

B. It counts the number of documents in each group.

C. It sorts the documents in ascending order.

D. It returns the first value of a specified field in each group.

Solution

1. Step 1: Understand the role of $first

   The $first accumulator returns the first value of a specified field from documents in a group.

2. Step 2: Differentiate from other accumulators

   Unlike $last, which returns the last value, $first picks the earliest value based on the document order.

3. Final Answer:

   It returns the first value of a specified field in each group. -> Option D

4. Quick Check:

   $first = first value [OK]

Hint: Remember: $first picks earliest value in group [OK]

Common Mistakes:

• Confusing $first with $last accumulator
• Thinking $first sorts documents automatically
• Assuming $first counts documents

2. Which of the following is the correct syntax to use $last in a MongoDB aggregation $group stage?

easy

A. { $group: { _id: "$category", lastItem: { last: "$item" } } }

B. { $group: { _id: "$category", lastItem: { $first: "$item" } } }

C. { $group: { _id: "$category", lastItem: { $last: "$item" } } }

D. { $group: { _id: "$category", lastItem: { $last: item } } }

Solution

1. Step 1: Identify correct accumulator usage

   The $last accumulator must be used with a field path as a string, like "$item".

2. Step 2: Check syntax correctness

   { $group: { _id: "$category", lastItem: { $last: "$item" } } } correctly uses $last: "$item" inside $group. Options A and C have syntax errors, and B uses $first instead of $last.

3. Final Answer:

   { $group: { _id: "$category", lastItem: { $last: "$item" } } } -> Option C

4. Quick Check:

   Correct $last syntax = { $group: { _id: "$category", lastItem: { $last: "$item" } } } [OK]

Hint: Use $last with field path as string "$field" [OK]

Common Mistakes:

• Omitting quotes around field name
• Using $first instead of $last by mistake
• Writing accumulator name incorrectly

3. Given the collection sales with documents:

{ "_id": 1, "product": "A", "date": "2023-01-01" },
{ "_id": 2, "product": "A", "date": "2023-01-05" },
{ "_id": 3, "product": "B", "date": "2023-01-02" }

What will be the output of this aggregation?

[
  { $sort: { date: 1 } },
  { $group: { _id: "$product", firstSale: { $first: "$date" }, lastSale: { $last: "$date" } } }
]

medium

A. [{ "_id": "A", "firstSale": "2023-01-05", "lastSale": "2023-01-01" }, { "_id": "B", "firstSale": "2023-01-02", "lastSale": "2023-01-02" }]

B. [{ "_id": "A", "firstSale": "2023-01-01", "lastSale": "2023-01-05" }, { "_id": "B", "firstSale": "2023-01-02", "lastSale": "2023-01-02" }]

C. [{ "_id": "A", "firstSale": "2023-01-01", "lastSale": "2023-01-01" }, { "_id": "B", "firstSale": "2023-01-02", "lastSale": "2023-01-02" }]

D. [{ "_id": "A", "firstSale": null, "lastSale": null }, { "_id": "B", "firstSale": null, "lastSale": null }]

Solution

1. Step 1: Sort documents by date ascending

   The $sort stage orders documents by date: A on 2023-01-01, B on 2023-01-02, A on 2023-01-05.

2. Step 2: Group by product and apply $first and $last

   For product A, firstSale is earliest date "2023-01-01", lastSale is latest date "2023-01-05". For B, both firstSale and lastSale are "2023-01-02".

3. Final Answer:

   [{ "_id": "A", "firstSale": "2023-01-01", "lastSale": "2023-01-05" }, { "_id": "B", "firstSale": "2023-01-02", "lastSale": "2023-01-02" }] -> Option B

4. Quick Check:

   Sorted + grouped $first/$last = [{ "_id": "A", "firstSale": "2023-01-01", "lastSale": "2023-01-05" }, { "_id": "B", "firstSale": "2023-01-02", "lastSale": "2023-01-02" }] [OK]

Hint: Sort first, then $first/$last pick earliest/latest values [OK]

Common Mistakes:

• Ignoring $sort stage before $group
• Mixing up $first and $last values
• Assuming $first/$last pick random values

4. You wrote this aggregation but it returns incorrect $last values:

db.orders.aggregate([
  { $group: { _id: "$customer", lastOrder: { $last: "$orderDate" } } }
])

What is the most likely reason?

medium

A. You forgot to sort documents by orderDate before grouping.

B. You used $last instead of $first.

C. The orderDate field is missing in documents.

D. You need to use $max instead of $last.

Solution

1. Step 1: Understand $last depends on document order

   The $last accumulator returns the last value in the current document order within each group.

2. Step 2: Identify missing $sort stage

   Without sorting by orderDate, documents are in arbitrary order, so $last may not return the latest date.

3. Final Answer:

   You forgot to sort documents by orderDate before grouping. -> Option A

4. Quick Check:

   Missing $sort before $group causes wrong $last [OK]

Hint: Always $sort by date before using $last [OK]

Common Mistakes:

• Assuming $last finds max value automatically
• Using $last without sorting documents
• Confusing $last with $max accumulator

5. You have a collection events with documents:

{ "user": "Alice", "timestamp": 100 },
{ "user": "Alice", "timestamp": 200 },
{ "user": "Bob", "timestamp": 150 },
{ "user": "Bob", "timestamp": 50 }

You want to find each user's first and last event timestamps using the $first and $last accumulators. Which aggregation pipeline correctly achieves this?

hard

A. [ { $sort: { timestamp: 1 } }, { $group: { _id: "$user", firstEvent: { $first: "$timestamp" }, lastEvent: { $last: "$timestamp" } } } ]

B. [ { $group: { _id: "$user", firstEvent: { $first: "$timestamp" }, lastEvent: { $last: "$timestamp" } } } ]

C. [ { $sort: { timestamp: -1 } }, { $group: { _id: "$user", firstEvent: { $first: "$timestamp" }, lastEvent: { $last: "$timestamp" } } } ]

D. [ { $group: { _id: "$user", firstEvent: { $min: "$timestamp" }, lastEvent: { $max: "$timestamp" } } } ]

Solution

1. Step 1: Sort events by timestamp ascending

   Sorting by timestamp: 1 ensures earliest events come first for each user.

2. Step 2: Group by user and use $first and $last

   Using $first and $last after sorting picks the earliest and latest timestamps correctly.

3. Final Answer:

   [ { $sort: { timestamp: 1 } }, { $group: { _id: "$user", firstEvent: { $first: "$timestamp" }, lastEvent: { $last: "$timestamp" } } } ] -> Option A

4. Quick Check:

   Sort ascending + $first/$last = [ { $sort: { timestamp: 1 } }, { $group: { _id: "$user", firstEvent: { $first: "$timestamp" }, lastEvent: { $last: "$timestamp" } } } ] [OK]

Hint: Sort ascending before grouping to get correct first/last [OK]

Common Mistakes:

• Skipping $sort stage before $group
• Sorting descending when $first expects ascending
• Using $min/$max instead of $first/$last when order matters