• Reference >
• Operators >
• Aggregation Pipeline Stages >
• $unionWith (aggregation)

$unionWith (aggregation)

Definition

$unionWith

New in version 4.4.

Performs a union of two collections; i.e. $unionWith combines pipeline results from two collections into a single result set. The stage outputs the combined result set (including duplicates) to the next stage.

Syntax

The $unionWith stage has the following syntax:

copy

{ $unionWith: { coll: "<collection>", pipeline: [ <stage1>, ... ] } }

To include all documents from the specified collection without any processing, you can use the simplified form:

copy

{ $unionWith: "<collection>" }  // Include all documents from the specified collection

The $unionWith stage takes a document with the following fields:

Field	Description
coll	The collection or view whose pipeline results you wish to include in the result set.
pipeline	Optional. An aggregation pipeline to apply to the specified coll. [ <stage1>, <stage2>, ...] The pipeline cannot include the $out and $merge stages.

The $unionWith operation would correspond to the following SQL statement:

copy

SELECT *
FROM Collection1
WHERE ...
UNION ALL
SELECT *
FROM Collection2
WHERE ...

Considerations

Duplicates Results

The combined results from the previous stage and the $unionWith stage can include duplicates.

For example, create a suppliers collection:

copy

db.suppliers.insertMany([
  { _id: 1, supplier: "Aardvark and Sons", state: "Texas" },
  { _id: 2, supplier: "Bears Run Amok.", state: "Colorado"},
  { _id: 3, supplier: "Squid Mark Inc. ", state: "Rhode Island" },
])

copy

db.warehouses.insertMany([
  { _id: 1, warehouse: "A", region: "West", state: "California" },
  { _id: 2, warehouse: "B", region: "Central", state: "Colorado"},
  { _id: 3, warehouse: "C", region: "East", state: "Florida" },
])

The following aggregation which combines the results from the state field projection from the suppliers collection with the results from the state field projection from the warehouse collection:

copy

db.suppliers.aggregate([
   { $project: { state: 1, _id: 0 } },
   { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} }
])

As can be seen from the returned documents, the result set contains duplicates:

copy

{ "state" : "Texas" }
{ "state" : "Colorado" }
{ "state" : "Rhode Island" }
{ "state" : "California" }
{ "state" : "Colorado" }
{ "state" : "Florida" }

To remove the duplicates, you can include a $group stage to group by the state field:

copy

db.suppliers.aggregate([
   { $project: { state: 1, _id: 0 } },
   { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} },
   { $group: { _id: "$state" } }
])

The result set no longer contains duplicates:

copy

{ "_id" : "California" }
{ "_id" : "Texas" }
{ "_id" : "Florida" }
{ "_id" : "Colorado" }
{ "_id" : "Rhode Island" }

$unionWith a Sharded Collection

If the $unionWith stage is part of the $lookup pipeline, the $unionWith coll cannot be sharded. For example, in the following aggregation operation, the inventory_q1 collection cannot be sharded:

db.suppliers.aggregate([
   {
      $lookup: {
         from: "warehouses",
         let: { order_item: "$item", order_qty: "$ordered" },
         pipeline: [
            ...
            { $unionWith: { coll: "inventory_q1", pipeline: [ ... ] } },
            ...
         ],
         as: "stockdata"
      }
   }
])

Collation

If the db.collection.aggregate() includes a collation, that collation is used for the operation, ignoring any other collations.

If the db.collection.aggregate() does not include a collation, the db.collection.aggregate() method uses the collation for the top-level collection/view on which the db.collection.aggregate() is run:

• If the $unionWith coll is a collection, its collation is ignored.
• If the $unionWith coll is a view, then its collation must match that of the top-level collection/view. Otherwise, the operation errors.

Restrictions

Restrictions	Description
Transactions	An aggregation pipeline cannot use $unionWith inside transactions.
Sharded Collection	If the $unionWith stage is part of the $lookup pipeline, the $unionWith coll cannot be sharded.
$out	The $unionWith pipeline cannot include the $out stage.
$merge	The $unionWith pipeline cannot include the $merge stage.

Examples

Create a Yearly Report from the Union of Quarterly Data Collections

Create a sample sales2019q1 collection with the following documents:

copy

db.sales2019q1.insertMany([
  { store: "A", item: "Chocolates", quantity: 150 },
  { store: "B", item: "Chocolates", quantity: 50 },
  { store: "A", item: "Cookies", quantity: 100 },
  { store: "B", item: "Cookies", quantity: 120 },
  { store: "A", item: "Pie", quantity: 10 },
  { store: "B", item: "Pie", quantity: 5 }
])

Create a sample sales2019q2 collection with the following documents:

copy

db.sales2019q2.insertMany([
  { store: "A", item: "Cheese", quantity: 30 },
  { store: "B", item: "Cheese", quantity: 50 },
  { store: "A", item: "Chocolates", quantity: 125 },
  { store: "B", item: "Chocolates", quantity: 150 },
  { store: "A", item: "Cookies", quantity: 200 },
  { store: "B", item: "Cookies", quantity: 100 },
  { store: "B", item: "Nuts", quantity: 100 },
  { store: "A", item: "Pie", quantity: 30 },
  { store: "B", item: "Pie", quantity: 25 }
])

Create a sample sales2019q3 collection with the following documents:

copy

db.sales2019q3.insertMany([
  { store: "A", item: "Cheese", quantity: 50 },
  { store: "B", item: "Cheese", quantity: 20 },
  { store: "A", item: "Chocolates", quantity: 125 },
  { store: "B", item: "Chocolates", quantity: 150 },
  { store: "A", item: "Cookies", quantity: 200 },
  { store: "B", item: "Cookies", quantity: 100 },
  { store: "A", item: "Nuts", quantity: 80 },
  { store: "B", item: "Nuts", quantity: 30 },
  { store: "A", item: "Pie", quantity: 50 },
  { store: "B", item: "Pie", quantity: 75 }
])

Create a sample sales2019q4 collection with the following documents:

copy

db.sales2019q4.insertMany([
  { store: "A", item: "Cheese", quantity: 100, },
  { store: "B", item: "Cheese", quantity: 100},
  { store: "A", item: "Chocolates", quantity: 200 },
  { store: "B", item: "Chocolates", quantity: 300 },
  { store: "A", item: "Cookies", quantity: 500 },
  { store: "B", item: "Cookies", quantity: 400 },
  { store: "A", item: "Nuts", quantity: 100 },
  { store: "B", item: "Nuts", quantity: 200 },
  { store: "A", item: "Pie", quantity: 100 },
  { store: "B", item: "Pie", quantity: 100 }
])

Report 1: All Sales by Quarter and Stores and Items

The following aggregation uses $unionWith to combine documents from all four collections to create a yearly sales report that lists all sales by quarter and stores:

copy

db.sales2019q1.aggregate( [
   { $set: { _id: "2019Q1" } },
   { $unionWith: { coll: "sales2019q2", pipeline: [ { $set: { _id: "2019Q2" } } ] } },
   { $unionWith: { coll: "sales2019q3", pipeline: [ { $set: { _id: "2019Q3" } } ] } },
   { $unionWith: { coll: "sales2019q4", pipeline: [ { $set: { _id: "2019Q4" } } ] } },
   { $sort: { _id: 1, store: 1, item: 1 } }
] )

Specifically, the aggregation pipeline uses:

• a $set stage to update the _id field to contain the quarter. That is, the documents from this stage has the form:

  { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 }
  ...
  

• a sequence of $unionWith stages to combine all documents from the four collections; each also using the $set stage on its documents. That is, the documents are from all four collections and have the form:

  { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 }
  ...
  { "_id" : "2019Q2", "store" : "A", "item" : "Cheese", "quantity" : 30 }
  { "_id" : "2019Q2", "store" : "B", "item" : "Cheese", "quantity" : 50 }
  ...
  { "_id" : "2019Q3", "store" : "A", "item" : "Cheese", "quantity" : 50 }
  { "_id" : "2019Q3", "store" : "B", "item" : "Cheese", "quantity" : 20 }
  ...
  { "_id" : "2019Q4", "store" : "A", "item" : "Cheese", "quantity" : 100 }
  { "_id" : "2019Q4", "store" : "B", "item" : "Cheese", "quantity" : 100 }
  

• a $sort stage to sort by the _id (i.e. the quarter), the store, and item.

  { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : "2019Q1", "store" : "A", "item" : "Cookies", "quantity" : 100 }
  { "_id" : "2019Q1", "store" : "A", "item" : "Pie", "quantity" : 10 }
  { "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 }
  { "_id" : "2019Q1", "store" : "B", "item" : "Cookies", "quantity" : 120 }
  { "_id" : "2019Q1", "store" : "B", "item" : "Pie", "quantity" : 5 }
  { "_id" : "2019Q2", "store" : "A", "item" : "Cheese", "quantity" : 30 }
  { "_id" : "2019Q2", "store" : "A", "item" : "Chocolates", "quantity" : 125 }
  { "_id" : "2019Q2", "store" : "A", "item" : "Cookies", "quantity" : 200 }
  { "_id" : "2019Q2", "store" : "A", "item" : "Pie", "quantity" : 30 }
  { "_id" : "2019Q2", "store" : "B", "item" : "Cheese", "quantity" : 50 }
  { "_id" : "2019Q2", "store" : "B", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : "2019Q2", "store" : "B", "item" : "Cookies", "quantity" : 100 }
  { "_id" : "2019Q2", "store" : "B", "item" : "Nuts", "quantity" : 100 }
  { "_id" : "2019Q2", "store" : "B", "item" : "Pie", "quantity" : 25 }
  { "_id" : "2019Q3", "store" : "A", "item" : "Cheese", "quantity" : 50 }
  { "_id" : "2019Q3", "store" : "A", "item" : "Chocolates", "quantity" : 125 }
  { "_id" : "2019Q3", "store" : "A", "item" : "Cookies", "quantity" : 200 }
  { "_id" : "2019Q3", "store" : "A", "item" : "Nuts", "quantity" : 80 }
  { "_id" : "2019Q3", "store" : "A", "item" : "Pie", "quantity" : 50 }
  { "_id" : "2019Q3", "store" : "B", "item" : "Cheese", "quantity" : 20 }
  { "_id" : "2019Q3", "store" : "B", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : "2019Q3", "store" : "B", "item" : "Cookies", "quantity" : 100 }
  { "_id" : "2019Q3", "store" : "B", "item" : "Nuts", "quantity" : 30 }
  { "_id" : "2019Q3", "store" : "B", "item" : "Pie", "quantity" : 75 }
  { "_id" : "2019Q4", "store" : "A", "item" : "Cheese", "quantity" : 100 }
  { "_id" : "2019Q4", "store" : "A", "item" : "Chocolates", "quantity" : 200 }
  { "_id" : "2019Q4", "store" : "A", "item" : "Cookies", "quantity" : 500 }
  { "_id" : "2019Q4", "store" : "A", "item" : "Nuts", "quantity" : 100 }
  { "_id" : "2019Q4", "store" : "A", "item" : "Pie", "quantity" : 100 }
  { "_id" : "2019Q4", "store" : "B", "item" : "Cheese", "quantity" : 100 }
  { "_id" : "2019Q4", "store" : "B", "item" : "Chocolates", "quantity" : 300 }
  { "_id" : "2019Q4", "store" : "B", "item" : "Cookies", "quantity" : 400 }
  { "_id" : "2019Q4", "store" : "B", "item" : "Nuts", "quantity" : 200 }
  { "_id" : "2019Q4", "store" : "B", "item" : "Pie", "quantity" : 100 }
  

Report 2: Aggregated Yearly Sales by Items

The following aggregation uses $unionWith to combine documents from all four collections to create a yearly sales report that lists the yearly sales quantity per item:

copy

db.sales2019q1.aggregate( [
   { $unionWith: "sales2019q2" },
   { $unionWith: "sales2019q3" },
   { $unionWith: "sales2019q4" },
   { $group: { _id: "$item", total: { $sum: "$quantity" } } },
   { $sort: { total: -1 }}
] )

• The sequence of $unionWith stages retrieve documents from the specified collections into the pipeline:

  { "_id" : ObjectId("5e505848c15f157c0793fec7"), "store" : "A", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : ObjectId("5e505848c15f157c0793fec8"), "store" : "B", "item" : "Chocolates", "quantity" : 50 }
  { "_id" : ObjectId("5e505848c15f157c0793fec9"), "store" : "A", "item" : "Cookies", "quantity" : 100 }
  { "_id" : ObjectId("5e505848c15f157c0793feca"), "store" : "B", "item" : "Cookies", "quantity" : 120 }
  { "_id" : ObjectId("5e505848c15f157c0793fecb"), "store" : "A", "item" : "Pie", "quantity" : 10 }
  { "_id" : ObjectId("5e505848c15f157c0793fecc"), "store" : "B", "item" : "Pie", "quantity" : 5 }
  { "_id" : ObjectId("5e50584bc15f157c0793fecd"), "store" : "A", "item" : "Cheese", "quantity" : 30 }
  { "_id" : ObjectId("5e50584bc15f157c0793fece"), "store" : "B", "item" : "Cheese", "quantity" : 50 }
  { "_id" : ObjectId("5e50584bc15f157c0793fecf"), "store" : "A", "item" : "Chocolates", "quantity" : 125 }
  { "_id" : ObjectId("5e50584bc15f157c0793fed0"), "store" : "B", "item" : "Chocolates", "quantity" : 150 }
  { "_id" : ObjectId("5e50584bc15f157c0793fed1"), "store" : "A", "item" : "Cookies", "quantity" : 200 }
  { "_id" : ObjectId("5e50584bc15f157c0793fed2"), "store" : "B", "item" : "Cookies", "quantity" : 100 }
  ...
  

• The $group stage groups by the item field and uses $sum to calculate the yearly total sales quantity per item:

  { "_id" : "Cookies", "total" : 1720 }
  { "_id" : "Pie", "total" : 395 }
  { "_id" : "Cheese", "total" : 350 }
  { "_id" : "Chocolates", "total" : 1250 }
  { "_id" : "Nuts", "total" : 510 }
  

• The $sort stage orders the documents by descending total.

  { "_id" : "Cookies", "total" : 1940 }
  { "_id" : "Chocolates", "total" : 1450 }
  { "_id" : "Nuts", "total" : 510 }
  { "_id" : "Pie", "total" : 410 }
  { "_id" : "Cheese", "total" : 350 }
  

Alternatively, you could specify the $group stage within each $unionWith stage:

copy

db.sales2019q1.aggregate( [
   { $group: { _id: "$item", total: { $sum: "$quantity" } } },
   { $unionWith: { coll: "sales2019q2", pipeline: [ { $group: { _id: "$item", total: { $sum: "$quantity" } } } ] } },
   { $unionWith: { coll: "sales2019q3", pipeline: [ { $group: { _id: "$item", total: { $sum: "$quantity" } } } ] } },
   { $unionWith: { coll: "sales2019q4", pipeline: [ { $group: { _id: "$item", total: { $sum: "$quantity" } } } ] } },
   { $group: { _id: "$_id", total: { $sum: "$total" } } },
   { $sort: { total: -1 }}
] )

• The first $group groups the 2019q1 sales totals by items:

  { "_id" : "Pie", "total" : 30 }
  { "_id" : "Cookies", "total" : 440 }
  { "_id" : "Chocolates", "total" : 400 }
  

• The sequence of $unionWith stages groups the sales total by the items from the specified collections into the pipeline:

  { "_id" : "Cookies", "total" : 440 }
  { "_id" : "Chocolates", "total" : 400 }
  { "_id" : "Pie", "total" : 30 }
  { "_id" : "Chocolates", "total" : 275 }  // From sales2019q2
  { "_id" : "Nuts", "total" : 100 }        // From sales2019q2
  { "_id" : "Cheese", "total" : 80 }       // From sales2019q2
  { "_id" : "Pie", "total" : 55 }          // From sales2019q2
  { "_id" : "Cookies", "total" : 300 }     // From sales2019q2
  ...                                      // Results from sales2019 q3 and q4 omitted for brevity
  

• The last $group stage groups these quarterly groupings:

  { "_id" : "Cookies", "total" : 1720 }
  { "_id" : "Pie", "total" : 395 }
  { "_id" : "Cheese", "total" : 350 }
  { "_id" : "Chocolates", "total" : 1250 }
  { "_id" : "Nuts", "total" : 510 }
  

• The $sort stage orders the documents by descending total.

  { "_id" : "Cookies", "total" : 1940 }
  { "_id" : "Chocolates", "total" : 1450 }
  { "_id" : "Nuts", "total" : 510 }
  { "_id" : "Pie", "total" : 410 }
  { "_id" : "Cheese", "total" : 350 }
  

←   $sortByCount (aggregation) $unset (aggregation)  →

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.