$first and $last accumulators in MongoDB - Mini Project: Build & Apply

$first and $last Accumulators in MongoDB Aggregation

📖 Scenario: You work at a bookstore that tracks sales data in a MongoDB collection. Each sale record includes the book title, the date of sale, and the price. You want to analyze the sales to find the first and last sale price for each book.

🎯 Goal: Build a MongoDB aggregation pipeline that groups sales by book title and uses the $first and $last accumulators to find the first and last sale price for each book.

📋 What You'll Learn

Create a collection called sales with documents containing title, date, and price fields.

Add a variable sortOrder to specify sorting by date ascending.

Write an aggregation pipeline that groups sales by title and uses $first and $last to get the first and last price per book.

Complete the aggregation pipeline by adding the $sort stage to order results by title.

💡 Why This Matters

🌍 Real World

Bookstores and retailers often analyze sales data to understand price changes over time for products.

💼 Career

Knowing how to use MongoDB aggregation with $first and $last is useful for data analysts and backend developers working with time-series or ordered data.

1

Create the sales collection with sample data

Create a variable called sales and assign it an array of documents with these exact entries: { title: "Book A", date: ISODate("2023-01-01"), price: 10 }, { title: "Book A", date: ISODate("2023-01-05"), price: 12 }, { title: "Book B", date: ISODate("2023-01-02"), price: 15 }, { title: "Book B", date: ISODate("2023-01-10"), price: 18 }

MongoDB

const sales = [
  // Your documents here
]

Hint

Use new Date("YYYY-MM-DD") to create date objects in JavaScript.

2

Add a sortOrder variable to sort by date ascending

Create a variable called sortOrder and assign it an object that sorts by date in ascending order using { date: 1 }.

MongoDB

const sales = [
  { title: "Book A", date: new Date("2023-01-01"), price: 10 },
  { title: "Book A", date: new Date("2023-01-05"), price: 12 },
  { title: "Book B", date: new Date("2023-01-02"), price: 15 },
  { title: "Book B", date: new Date("2023-01-10"), price: 18 }
]

// Your code here

Hint

Sorting by date ascending means using 1 as the value.

3

Write an aggregation pipeline using $first and $last

Create a variable called pipeline and assign it an array with these stages: first a { $sort: sortOrder } stage, then a { $group } stage that groups by title and uses $first to get the first price as firstPrice and $last to get the last price as lastPrice.

MongoDB

const sales = [
  { title: "Book A", date: new Date("2023-01-01"), price: 10 },
  { title: "Book A", date: new Date("2023-01-05"), price: 12 },
  { title: "Book B", date: new Date("2023-01-02"), price: 15 },
  { title: "Book B", date: new Date("2023-01-10"), price: 18 }
]

const sortOrder = { date: 1 }

// Your code here

Hint

Remember to sort before grouping to get correct $first and $last values.

4

Add a $sort stage to order results by title

Add a final stage to the pipeline array that sorts the grouped results by _id in ascending order using { $sort: { _id: 1 } }.

MongoDB

const sales = [
  { title: "Book A", date: new Date("2023-01-01"), price: 10 },
  { title: "Book A", date: new Date("2023-01-05"), price: 12 },
  { title: "Book B", date: new Date("2023-01-02"), price: 15 },
  { title: "Book B", date: new Date("2023-01-10"), price: 18 }
]

const sortOrder = { date: 1 }

const pipeline = [
  { $sort: sortOrder },
  { $group: {
      _id: "$title",
      firstPrice: { $first: "$price" },
      lastPrice: { $last: "$price" }
    }
  }
]

// Your code here

Hint

Sorting by _id ascending orders the grouped results by book title.

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