$first and $last accumulators in MongoDB - Step-by-Step Execution

Concept Flow - $first and $last accumulators

Start with documents

↓

Sort documents if needed

↓

Group documents by key

↓

$first picks first document's field

↓

$last picks last document's field

↓

Output grouped results with $first and $last values

Documents are sorted if needed, then grouped by a key. $first and $last pick the first and last values from each group after sorting.

Execution Sample

MongoDB

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

This query sorts sales by date ascending, groups by store, then picks the first and last sale amounts per store.

Execution Table

Step	Input Document	Group Key (_id)	Sorted Order	$first Value	$last Value	Action
1	{store: 'A', date: '2023-01-01', amount: 100}	A	1	100	100	First document for group A, first and last both 100
2	{store: 'A', date: '2023-01-05', amount: 150}	A	2	100	150	Second document for A, last updated to 150
3	{store: 'B', date: '2023-01-02', amount: 200}	B	1	200	200	First document for B, first and last both 200
4	{store: 'B', date: '2023-01-10', amount: 250}	B	2	200	250	Second document for B, last updated to 250
5	Aggregation complete	-	-	100	150	Group A final first and last values
6	Aggregation complete	-	-	200	250	Group B final first and last values

💡 All documents processed, groups output with $first and $last values

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Group A $first	null	100	100	100	100	100
Group A $last	null	100	150	150	150	150
Group B $first	null	null	null	200	200	200
Group B $last	null	null	null	200	250	250

Key Moments - 2 Insights

Why does $first not change after the first document in a group?

$first always takes the value from the first document in the sorted group, so after the first document, it remains unchanged as shown in rows 1 and 2 of the execution_table.

How does sorting affect $first and $last values?

Sorting determines the order of documents in each group. $first picks the first document after sorting, and $last picks the last. Without sorting, the order is unpredictable.

Visual Quiz - 3 Questions

Look at the execution_table at Step 2, what is the $last value for group A?

A 100

B 150

C null

D 200

💡 Hint

Check the $last Value column in row 2 of the execution_table.

At which step does group B first get a $first value assigned?

A Step 1

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the Group Key and $first Value columns in the execution_table.

If the documents were not sorted by date, how would $first and $last behave?

A They would pick values from documents in an unpredictable order.

B They would still pick the earliest and latest dates correctly.

C They would cause an error.

D They would pick the same value for both $first and $last.

💡 Hint

Refer to the key_moments about sorting's effect on $first and $last.

Concept Snapshot

$first and $last accumulators in MongoDB aggregation:
- Use inside $group stage
- $first returns the first value in the group (after sorting)
- $last returns the last value in the group (after sorting)
- Sorting documents before grouping controls which values are first/last
- Useful to get earliest/latest values per group

Full Transcript

This visual execution trace shows how MongoDB's $first and $last accumulators work inside an aggregation pipeline. Documents are first sorted by date, then grouped by store. For each group, $first picks the amount from the earliest date document, and $last picks the amount from the latest date document. The execution table tracks each document processed, the group it belongs to, and how $first and $last values update. The variable tracker shows these values step-by-step. Key moments clarify why $first stays the same after the first document and how sorting affects results. The quiz tests understanding of these steps and the importance of sorting. This helps beginners see how $first and $last pick values from grouped documents in order.

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