$first and $last accumulators in MongoDB

Introduction

These accumulators help you pick the first or last value from a group of data. They are useful when you want to find the earliest or latest item in a list.

When you want to find the first sale date for each product.

When you want to get the last comment made on a post.

When you want to find the first or last entry in a sorted list of records.

When you want to summarize data by picking the earliest or latest value in each group.

Syntax

MongoDB

{ $group: { _id: <grouping_key>, firstValue: { $first: <expression> }, lastValue: { $last: <expression> } } }

The $first and $last accumulators return the value of the first or last document in the group, based on the order of documents entering the $group stage.

To control which document is first or last, use a $sort stage before $group.

Examples

This groups documents by category and picks the first product in each category.

MongoDB

{ $group: { _id: "$category", firstProduct: { $first: "$product" } } }

This groups documents by user and picks the last login date for each user.

MongoDB

{ $group: { _id: "$user", lastLogin: { $last: "$loginDate" } } }

Sample Program

This query sorts sales by date ascending, then groups by product. It finds the first and last sale date for each product.

MongoDB

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

Important Notes

Always sort your data before using $first or $last to get meaningful results.

If you do not sort, the first and last values depend on the natural order of documents, which may be unpredictable.

Summary

$first and $last pick the first or last value in a group.

Use $sort before $group to control which values are first or last.

They help summarize data by earliest or latest values.

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