Docs Home → Develop Applications → MongoDB Manual

$unionWith (aggregation)

On this page

Definition

Syntax
Considerations
Examples

This version of the documentation is archived and no longer supported. View the current documentation to learn how to upgrade your version of MongoDB server.

Definition

$unionWith

New in version 4.4.

Performs a union of two collections. $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.

The order in which the combined result set documents are output is unspecified.

Syntax

The $unionWith stage has the following syntax:

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

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

{ $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:

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:

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" },
])

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:

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:

{ "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:

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:

{ "_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:

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:

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:

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:

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:

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:

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:

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 }