Docs Menu

$unionWith (aggregation)

On this page

  • Definition
  • Syntax
  • Considerations
  • Examples
$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.

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

The collection or view whose pipeline results you wish to include in the result set.

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 ...

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" }

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

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
Description
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.
The $unionWith pipeline cannot include the $out stage.
The $unionWith pipeline cannot include the $merge stage.

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

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 }

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 }
  • 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 }
Give Feedback
© 2021 MongoDB, Inc.

About

  • Careers
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.