$out and $merge for writing results in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What does this $out stage do?

Consider this aggregation pipeline on a collection named sales:

[{ $match: { status: "completed" } }, { $out: "completed_sales" }]

What is the result after running this pipeline?

MongoDB

[{ $match: { status: "completed" } }, { $out: "completed_sales" }]

A It creates or replaces the collection 'completed_sales' with documents where status is 'completed'.

B It appends documents with status 'completed' to the existing 'completed_sales' collection.

C It updates documents in 'completed_sales' to have status 'completed'.

D It returns documents with status 'completed' without modifying any collection.

💡 Hint

Think about what $out does to the target collection.

The $out stage writes the aggregation results to a specified collection, replacing it if it exists. Here, it creates or replaces 'completed_sales' with only documents where status is 'completed'.

❓ query_result

intermediate

What happens when using $merge with 'whenMatched: replace'?

Given this pipeline:

[{ $match: { category: "books" } }, { $merge: { into: "inventory", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }]

What is the effect on the 'inventory' collection?

MongoDB

[{ $match: { category: "books" } }, { $merge: { into: "inventory", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }]

A All documents are appended to 'inventory' without checking _id.

B Documents with matching _id are merged by adding fields; new documents are ignored.

C Documents with matching _id are replaced; new documents are inserted into 'inventory'.

D Documents with matching _id are updated by incrementing fields; new documents are rejected.

💡 Hint

Check the meaning of 'whenMatched: replace' and 'whenNotMatched: insert'.

The $merge stage merges documents into the target collection. With whenMatched: 'replace', matching documents are fully replaced. With whenNotMatched: 'insert', new documents are added.

📝 Syntax

advanced

Which $merge option syntax is invalid?

Identify the option that will cause a syntax error when used in a $merge stage:

MongoDB

{ $merge: { into: "users", on: "userId", whenMatched: "merge", whenNotMatched: "insert" } }

A { $merge: { into: "users", on: ["userId", "email"], whenMatched: "keepExisting", whenNotMatched: "insert" } }

B { $merge: { into: "users", on: "userId", whenMatched: "update", whenNotMatched: "insert" } }

C { $merge: { into: "users", on: "userId", whenMatched: "fail", whenNotMatched: "discard" } }

D { $merge: { into: "users", on: "userId", whenMatched: "merge", whenNotMatched: "insert" } }

💡 Hint

Check the valid values for 'whenMatched'.

The valid whenMatched values are 'replace', 'merge', 'keepExisting', and 'fail'. 'update' is not valid and causes a syntax error.

❓ optimization

advanced

Optimizing $merge for large datasets

You want to merge a large aggregation result into a collection without replacing entire documents, only updating specific fields. Which $merge option is best for this?

A Use 'whenMatched: "merge"' to update only specified fields and 'whenNotMatched: "insert"'.

B Use 'whenMatched: "replace"' to replace entire documents for consistency.

C Use 'whenMatched: "fail"' to avoid any updates and only insert new documents.

D Use 'whenMatched: "keepExisting"' to keep old documents and ignore new data.

💡 Hint

Think about updating only parts of documents efficiently.

whenMatched: 'merge' updates only the fields in the incoming documents, which is efficient for large datasets where full replacement is costly.

🧠 Conceptual

expert

Difference between $out and $merge in concurrency

Which statement best describes the difference between $out and $merge regarding concurrent writes and atomicity?

A Both <code>$out</code> and <code>$merge</code> block all concurrent writes until operation completes.

B <code>$merge</code> atomically replaces the entire target collection; <code>$out</code> merges documents individually allowing concurrent writes.

C Neither <code>$out</code> nor <code>$merge</code> guarantee atomicity or affect concurrent writes.

D <code>$out</code> atomically replaces the entire target collection, blocking concurrent writes; <code>$merge</code> allows concurrent writes by merging documents individually.

💡 Hint

Consider how each stage writes results to the target collection.

$out replaces the entire collection atomically, which blocks concurrent writes during the operation. $merge updates or inserts documents individually, allowing concurrent writes to other documents.

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