$unionWith (aggregation)
On this page
- Definition
- Syntax
- Considerations
- Duplicate Results
$unionWith
a Sharded Collection- Collation
- Atlas Search Support
- Restrictions
- Examples
- Create Sales Reports from the Union of Yearly Data Collections
- Report 1: All Sales by Year and Stores and Items
- Report 2: Aggregated Sales by Items
- Create a Union with Specified Documents
- Namespaces in Subpipelines
Definition
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 | Necessity | Description |
---|---|---|
Required if pipeline is omitted. Otherwise optional. | The collection or view whose pipeline results you wish to include in the result set. If you omit the | |
Required if coll is omitted. Otherwise optional. | An aggregation pipeline to apply to the input documents.
The pipeline cannot include the |
The $unionWith
operation would correspond to the following
SQL statement:
SELECT * FROM Collection1 WHERE ... UNION ALL SELECT * FROM Collection2 WHERE ...
Considerations
Duplicate Results
The combined results from the previous stage and the
$unionWith
stage can include duplicates.
For example, create a suppliers
collection and a warehouses
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 combines the state
field projection results from
the suppliers
and warehouse
collections.
db.suppliers.aggregate([ { $project: { state: 1, _id: 0 } }, { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} } ])
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
document, that collation is used for the operation, ignoring any other
collations.
If the db.collection.aggregate()
does not include a
collation
document, 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 MongoDB 6.0, you can specify the Atlas Search $search
or $searchMeta
stage
in the $unionWith
pipeline to search collections on the Atlas
cluster. The $search
or the $searchMeta
stage
must be the first stage inside the $unionWith
pipeline.
[{ "$unionWith": { "coll": <collection-name>, "pipeline": [{ "$search": { "<operator>": { <operator-specification> } }, ... }] } }]
[{ "$unionWith": { "coll": <collection-name>, "pipeline": [{ "$searchMeta": { "<collector>": { <collector-specification> } }, ... }] } }]
To see an example of $unionWith
with $search
,
see the Atlas Search tutorial Run an Atlas Search $search Query
Using $unionWith.
Restrictions
Restrictions | Description |
---|---|
An aggregation pipeline cannot use $unionWith inside
transactions. | |
Sharded Collection | |
The $unionWith pipeline cannot
include the $out stage. | |
The $unionWith pipeline cannot
include the $merge stage. |
Examples
Create Sales Reports from the Union of Yearly Data Collections
The following examples use the $unionWith
stage to combine data and
return results from multiple collections. In these examples, each collection
contains a year of sales data.
Populate Sample Data
Create a
sales_2017
collection with the following documents:db.sales_2017.insertMany( [ { store: "General Store", item: "Chocolates", quantity: 150 }, { store: "ShopMart", item: "Chocolates", quantity: 50 }, { store: "General Store", item: "Cookies", quantity: 100 }, { store: "ShopMart", item: "Cookies", quantity: 120 }, { store: "General Store", item: "Pie", quantity: 10 }, { store: "ShopMart", item: "Pie", quantity: 5 } ] ) Create a
sales_2018
collection with the following documents:db.sales_2018.insertMany( [ { store: "General Store", item: "Cheese", quantity: 30 }, { store: "ShopMart", item: "Cheese", quantity: 50 }, { store: "General Store", item: "Chocolates", quantity: 125 }, { store: "ShopMart", item: "Chocolates", quantity: 150 }, { store: "General Store", item: "Cookies", quantity: 200 }, { store: "ShopMart", item: "Cookies", quantity: 100 }, { store: "ShopMart", item: "Nuts", quantity: 100 }, { store: "General Store", item: "Pie", quantity: 30 }, { store: "ShopMart", item: "Pie", quantity: 25 } ] ) Create a
sales_2019
collection with the following documents:db.sales_2019.insertMany( [ { store: "General Store", item: "Cheese", quantity: 50 }, { store: "ShopMart", item: "Cheese", quantity: 20 }, { store: "General Store", item: "Chocolates", quantity: 125 }, { store: "ShopMart", item: "Chocolates", quantity: 150 }, { store: "General Store", item: "Cookies", quantity: 200 }, { store: "ShopMart", item: "Cookies", quantity: 100 }, { store: "General Store", item: "Nuts", quantity: 80 }, { store: "ShopMart", item: "Nuts", quantity: 30 }, { store: "General Store", item: "Pie", quantity: 50 }, { store: "ShopMart", item: "Pie", quantity: 75 } ] ) Create a
sales_2020
collection with the following documents:db.sales_2020.insertMany( [ { store: "General Store", item: "Cheese", quantity: 100, }, { store: "ShopMart", item: "Cheese", quantity: 100}, { store: "General Store", item: "Chocolates", quantity: 200 }, { store: "ShopMart", item: "Chocolates", quantity: 300 }, { store: "General Store", item: "Cookies", quantity: 500 }, { store: "ShopMart", item: "Cookies", quantity: 400 }, { store: "General Store", item: "Nuts", quantity: 100 }, { store: "ShopMart", item: "Nuts", quantity: 200 }, { store: "General Store", item: "Pie", quantity: 100 }, { store: "ShopMart", item: "Pie", quantity: 100 } ] )
Report 1: All Sales by Year and Stores and Items
The following aggregation creates a yearly sales report that lists all sales by
quarter and stores. The pipeline uses $unionWith
to combine documents
from all four collections:
db.sales_2017.aggregate( [ { $set: { _id: "2017" } }, { $unionWith: { coll: "sales_2018", pipeline: [ { $set: { _id: "2018" } } ] } }, { $unionWith: { coll: "sales_2019", pipeline: [ { $set: { _id: "2019" } } ] } }, { $unionWith: { coll: "sales_2020", pipeline: [ { $set: { _id: "2020" } } ] } }, { $sort: { _id: 1, store: 1, item: 1 } } ] )
Specifically, the aggregation pipeline uses:
A
$set
stage to update the_id
field to contain the year.A sequence of
$unionWith
stages to combine all documents from the four collections, each also using the$set
stage on its documents.A
$sort
stage to sort by the_id
(the year), thestore
, anditem
.
Pipeline output:
{ "_id" : "2017", "store" : "General Store", "item" : "Chocolates", "quantity" : 150 } { "_id" : "2017", "store" : "General Store", "item" : "Cookies", "quantity" : 100 } { "_id" : "2017", "store" : "General Store", "item" : "Pie", "quantity" : 10 } { "_id" : "2017", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 50 } { "_id" : "2017", "store" : "ShopMart", "item" : "Cookies", "quantity" : 120 } { "_id" : "2017", "store" : "ShopMart", "item" : "Pie", "quantity" : 5 } { "_id" : "2018", "store" : "General Store", "item" : "Cheese", "quantity" : 30 } { "_id" : "2018", "store" : "General Store", "item" : "Chocolates", "quantity" : 125 } { "_id" : "2018", "store" : "General Store", "item" : "Cookies", "quantity" : 200 } { "_id" : "2018", "store" : "General Store", "item" : "Pie", "quantity" : 30 } { "_id" : "2018", "store" : "ShopMart", "item" : "Cheese", "quantity" : 50 } { "_id" : "2018", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 150 } { "_id" : "2018", "store" : "ShopMart", "item" : "Cookies", "quantity" : 100 } { "_id" : "2018", "store" : "ShopMart", "item" : "Nuts", "quantity" : 100 } { "_id" : "2018", "store" : "ShopMart", "item" : "Pie", "quantity" : 25 } { "_id" : "2019", "store" : "General Store", "item" : "Cheese", "quantity" : 50 } { "_id" : "2019", "store" : "General Store", "item" : "Chocolates", "quantity" : 125 } { "_id" : "2019", "store" : "General Store", "item" : "Cookies", "quantity" : 200 } { "_id" : "2019", "store" : "General Store", "item" : "Nuts", "quantity" : 80 } { "_id" : "2019", "store" : "General Store", "item" : "Pie", "quantity" : 50 } { "_id" : "2019", "store" : "ShopMart", "item" : "Cheese", "quantity" : 20 } { "_id" : "2019", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 150 } { "_id" : "2019", "store" : "ShopMart", "item" : "Cookies", "quantity" : 100 } { "_id" : "2019", "store" : "ShopMart", "item" : "Nuts", "quantity" : 30 } { "_id" : "2019", "store" : "ShopMart", "item" : "Pie", "quantity" : 75 } { "_id" : "2020", "store" : "General Store", "item" : "Cheese", "quantity" : 100 } { "_id" : "2020", "store" : "General Store", "item" : "Chocolates", "quantity" : 200 } { "_id" : "2020", "store" : "General Store", "item" : "Cookies", "quantity" : 500 } { "_id" : "2020", "store" : "General Store", "item" : "Nuts", "quantity" : 100 } { "_id" : "2020", "store" : "General Store", "item" : "Pie", "quantity" : 100 } { "_id" : "2020", "store" : "ShopMart", "item" : "Cheese", "quantity" : 100 } { "_id" : "2020", "store" : "ShopMart", "item" : "Chocolates", "quantity" : 300 } { "_id" : "2020", "store" : "ShopMart", "item" : "Cookies", "quantity" : 400 } { "_id" : "2020", "store" : "ShopMart", "item" : "Nuts", "quantity" : 200 } { "_id" : "2020", "store" : "ShopMart", "item" : "Pie", "quantity" : 100 }
Report 2: Aggregated Sales by Items
The following aggregation creates a sales report that lists the sales quantity
per item. The pipeline uses $unionWith
to combine documents from all
four years:
db.sales_2017.aggregate( [ { $unionWith: "sales_2018" }, { $unionWith: "sales_2019" }, { $unionWith: "sales_2020" }, { $group: { _id: "$item", total: { $sum: "$quantity" } } }, { $sort: { total: -1 } } ] )
The sequence of
$unionWith
stages retrieve documents from the specified collections into the pipeline:The
$group
stage groups by theitem
field and uses$sum
to calculate the total sales quantity peritem
.The
$sort
stage orders the documents by descendingtotal
.
Pipeline output:
{ "_id" : "Cookies", "total" : 1720 } { "_id" : "Chocolates", "total" : 1250 } { "_id" : "Nuts", "total" : 510 } { "_id" : "Pie", "total" : 395 } { "_id" : "Cheese", "total" : 350 }
Create a Union with Specified Documents
You can use $unionWith
to perform a union with documents that you
specify in the pipeline
field. When you specify a
$documents
stage in the pipeline
field, you perform a
union with documents that aren't stored in a separate collection.
Create a collection cakeFlavors
:
db.cakeFlavors.insertMany( [ { _id: 1, flavor: "chocolate" }, { _id: 2, flavor: "strawberry" }, { _id: 3, flavor: "cherry" } ] )
The following $unionWith
operation performs a union with documents
specified in the pipeline
$documents
field:
db.cakeFlavors.aggregate( [ { $unionWith: { pipeline: [ { $documents: [ { _id: 4, flavor: "orange" }, { _id: 5, flavor: "vanilla", price: 20 } ] } ] } } ] )
Output:
[ { _id: 1, flavor: 'chocolate' }, { _id: 2, flavor: 'strawberry' }, { _id: 3, flavor: 'cherry' }, { _id: 4, flavor: 'orange' }, { _id: 5, flavor: 'vanilla', price: 20 } ]
Namespaces in Subpipelines
Starting in MongoDB 8.0, namespaces in subpipelines within $lookup
and $unionWith
are validated to ensure the correct use of from
and coll
fields:
For
$lookup
, omit thefrom
field if you use a subpipeline with a stage which doesn't require a specified collection. For example, a$documents
stage.Similarly, for
$unionWith
, omit thecoll
field.
Unchanged behavior:
For a
$lookup
that starts with a stage for a collection, for example a$match
or$collStats
subpipeline, you must include thefrom
field and specify the collection.Similarly, for
$unionWith
, include thecoll
field and specify the collection.
The following scenario shows an example.
Create a collection cakeFlavors
:
db.cakeFlavors.insertMany( [ { _id: 1, flavor: "chocolate" }, { _id: 2, flavor: "strawberry" }, { _id: 3, flavor: "cherry" } ] )
Starting in MongoDB 8.0, the following example returns an error because
it contains an invalid coll
field:
db.cakeFlavors.aggregate( [ { $unionWith: { coll: "cakeFlavors", pipeline: [ { $documents: [] } ] } } ] )
In MongoDB versions before 8.0, the previous example runs.
For an example with a valid coll
field, see
Duplicate Results.