$unionWith (aggregation)¶

On this page

Definition
Syntax
Considerations
Examples
- Create a Yearly Report from the Union of Quarterly Data Collections

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:

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. Starting in v6.0, the pipeline can contain the Atlas Search $search stage as the first stage inside the pipeline. To learn more, see Atlas Search Support.

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.

Atlas Search Support¶

Starting in v6.0, the pipeline can contain the Atlas Search $search stage as the first stage inside the pipeline.

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) →

$unionWith (aggregation)¶

Definition¶

Syntax¶

Considerations¶

Duplicates Results¶

$unionWith a Sharded Collection¶

Collation¶

Atlas Search Support¶

Restrictions¶

Examples¶

Create a Yearly Report from the Union of Quarterly Data Collections¶

Report 1: All Sales by Quarter and Stores and Items¶

Report 2: Aggregated Yearly Sales by Items¶

`$unionWith` a Sharded Collection¶