$out and $merge for writing results in MongoDB - Mini Project: Build & Apply

$out and $merge for writing results in MongoDB

📖 Scenario: You work at a small online bookstore. You have a collection called sales that records each book sale with fields like title, author, and copies_sold. You want to create a summary collection that shows total copies sold per author.

🎯 Goal: Build an aggregation pipeline that groups sales by author and sums the copies sold. Then, write the results into a new collection using $out and update an existing collection using $merge.

📋 What You'll Learn

Create an aggregation pipeline on the sales collection.

Group documents by author and sum copies_sold as total_copies.

Use $out to write the grouped results into a new collection called author_sales_summary.

Use $merge to update or insert documents into an existing collection called author_sales_aggregate.

💡 Why This Matters

🌍 Real World

Summarizing sales data by author helps bookstores track popular authors and manage inventory efficiently.

💼 Career

Understanding $out and $merge is essential for database administrators and backend developers who manage data pipelines and reporting in MongoDB.

1

Create the sales collection with sample data

Create a variable called sales and insert these exact documents into it: {"title": "Book A", "author": "Alice", "copies_sold": 5}, {"title": "Book B", "author": "Bob", "copies_sold": 3}, {"title": "Book C", "author": "Alice", "copies_sold": 7}.

MongoDB

// Insert the exact documents into the sales collection
// Your code here

Hint

Use db.sales.insertMany([...]) with the exact documents listed.

2

Create the aggregation pipeline to group by author and sum copies sold

Create a variable called pipeline that contains an array with one stage: a $group stage grouping by _id as "$author" and summing copies_sold into total_copies.

MongoDB

db.sales.insertMany([
  {"title": "Book A", "author": "Alice", "copies_sold": 5},
  {"title": "Book B", "author": "Bob", "copies_sold": 3},
  {"title": "Book C", "author": "Alice", "copies_sold": 7}
])

// Create the aggregation pipeline array with a $group stage
// Your code here

Hint

Use $group with _id: "$author" and total_copies: { $sum: "$copies_sold" }.

3

Use $out to write the aggregation results to author_sales_summary

Run db.sales.aggregate() with the pipeline followed by a $out stage that writes to the collection author_sales_summary.

MongoDB

db.sales.insertMany([
  {"title": "Book A", "author": "Alice", "copies_sold": 5},
  {"title": "Book B", "author": "Bob", "copies_sold": 3},
  {"title": "Book C", "author": "Alice", "copies_sold": 7}
])

const pipeline = [
  {
    $group: {
      _id: "$author",
      total_copies: { $sum: "$copies_sold" }
    }
  }
]

// Add a $out stage to the pipeline and run aggregate
// Your code here

Hint

Add { $out: "author_sales_summary" } as the last stage in the pipeline and run db.sales.aggregate(pipeline).

4

Use $merge to update or insert into author_sales_aggregate

Modify the pipeline by replacing the $out stage with a $merge stage that merges into author_sales_aggregate using _id as the unique key. Then run db.sales.aggregate(pipeline).

MongoDB

db.sales.insertMany([
  {"title": "Book A", "author": "Alice", "copies_sold": 5},
  {"title": "Book B", "author": "Bob", "copies_sold": 3},
  {"title": "Book C", "author": "Alice", "copies_sold": 7}
])

const pipeline = [
  {
    $group: {
      _id: "$author",
      total_copies: { $sum: "$copies_sold" }
    }
  },
  // Replace $out with $merge stage here
]

// Run the aggregation
// Your code here

Hint

Use $merge with into, on, whenMatched, and whenNotMatched options.

Practice

1. What is the main difference between the $out and $merge stages in MongoDB aggregation?

easy

A. $out updates documents, and $merge deletes the target collection.

B. $out only works with sharded clusters, $merge does not.

C. $out replaces the entire target collection, while $merge updates or inserts documents based on matching keys.

D. $out appends documents without replacing, $merge replaces the collection.

Solution

1. Step 1: Understand $out behavior

   $out writes aggregation results by replacing the entire target collection with new data.

2. Step 2: Understand $merge behavior

   $merge writes results by updating existing documents or inserting new ones based on matching keys, preserving unmatched documents.

3. Final Answer:

   $out replaces collection; $merge updates/inserts by keys -> Option C

4. Quick Check:

   $out replaces, $merge merges [OK]

Hint: Remember: $out replaces, $merge merges by keys [OK]

Common Mistakes:

• Thinking $out appends data instead of replacing
• Confusing $merge with deleting collections
• Assuming $merge always replaces entire collection

2. Which of the following is the correct syntax to use $out in a MongoDB aggregation pipeline?

easy

A. { $out: "targetCollection" }

B. { $out: { collection: "targetCollection" } }

C. { $out: ["targetCollection"] }

D. { $out: { db: "targetDB" } }

Solution

1. Step 1: Recall $out syntax

   The $out stage requires the target collection name as a string directly, like { $out: "collectionName" }.

2. Step 2: Check options

   { $out: "targetCollection" } matches the correct syntax. The other options use incorrect formats such as extra objects or arrays.

3. Final Answer:

   { $out: "targetCollection" } -> Option A

4. Quick Check:

   $out needs collection name string [OK]

Hint: Use simple string for collection name in $out [OK]

Common Mistakes:

• Using object or array instead of string for collection name
• Trying to specify database inside $out stage
• Adding extra braces or brackets around collection name

3. Given the aggregation pipeline below, what will be the result in the summary collection after running it?

db.sales.aggregate([
  { $group: { _id: "$category", total: { $sum: "$amount" } } },
  { $merge: { into: "summary", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }
])

medium

A. The summary collection will remain unchanged.

B. The summary collection will be completely replaced with the aggregation results.

C. The aggregation will fail due to missing $out stage.

D. The summary collection will contain documents with total sales per category, replacing existing matching documents.

Solution

1. Step 1: Understand the $group stage

   This groups sales by category and sums amounts, producing documents like { _id: category, total: sum }.

2. Step 2: Understand the $merge stage

   $merge writes results into summary collection, replacing documents with matching _id or inserting new ones.

3. Final Answer:

   summary collection updated with total sales per category, replacing matches -> Option D

4. Quick Check:

   $merge updates or inserts by key [OK]

Hint: Remember $merge updates matching keys, inserts new [OK]

Common Mistakes:

• Thinking $merge replaces entire collection like $out
• Assuming aggregation fails without $out
• Believing collection stays unchanged after $merge

4. You run this aggregation pipeline but get an error:

db.orders.aggregate([
  { $match: { status: "shipped" } },
  { $out: { collection: "shippedOrders" } }
])

What is the likely cause of the error?

medium

A. You cannot use $out after $match stage.

B. The $out stage syntax is incorrect; it should be a string, not an object.

C. The $match stage is invalid and causes the error.

D. The target collection shippedOrders does not exist.

Solution

1. Step 1: Check $out syntax

   $out requires the target collection name as a string, not an object with a collection field.

2. Step 2: Analyze error cause

   Using { collection: "shippedOrders" } causes syntax error; correct usage is { $out: "shippedOrders" }.

3. Final Answer:

   Incorrect $out syntax using object instead of string -> Option B

4. Quick Check:

   $out needs string collection name [OK]

Hint: Use string for $out target, not object [OK]

Common Mistakes:

• Assuming $match causes error
• Thinking $out requires existing collection
• Believing $out cannot follow $match

5. You want to aggregate user activity data and save results into a collection named userStats. You want to update existing documents by userId and insert new ones if missing, but keep other fields unchanged when matched. Which $merge option should you use?

hard

A. { into: "userStats", on: "userId", whenMatched: "merge", whenNotMatched: "insert" }

B. { into: "userStats", on: "userId", whenMatched: "replace", whenNotMatched: "insert" }

C. { into: "userStats", on: "userId", whenMatched: "fail", whenNotMatched: "insert" }

D. { into: "userStats", on: "userId", whenMatched: "keepExisting", whenNotMatched: "discard" }

Solution

1. Step 1: Understand whenMatched options

   whenMatched: "merge" updates only specified fields, keeping others unchanged; replace overwrites entire document.

2. Step 2: Choose correct $merge options

   To update existing documents by userId and insert new ones, use whenMatched: "merge" and whenNotMatched: "insert".

3. Final Answer:

   { into: "userStats", on: "userId", whenMatched: "merge", whenNotMatched: "insert" } -> Option A

4. Quick Check:

   whenMatched: "merge" updates fields, inserts new [OK]

Hint: Use whenMatched: "merge" to update fields, not replace [OK]

Common Mistakes:

• Using replace which overwrites entire documents
• Choosing fail which errors on matches
• Using invalid whenNotMatched options