Docs Menu
Docs Home
/
MongoDB Manual
/ / /

$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
$unionWith

Changed in version 8.0.

Combines two aggregations into a single result set. $unionWith outputs the combined result set (including duplicates) to the next stage.

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

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
coll
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 coll field, you must specify a pipeline field with a first stage of $documents.

Required if coll is omitted. Otherwise optional.

An aggregation pipeline to apply to the input documents.

  • If you specify coll, the pipeline applies to the documents in coll.

  • If you omit coll, the pipeline applies to the documents in the pipeline's $documents stage. For an example, see Create a Union with Specified Documents.

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:

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

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

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

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.

  1. 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 }
    ] )
  2. 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 }
    ] )
  3. 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 }
    ] )
  4. 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 }
    ] )

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), the store, and item.

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 }

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 the item field and uses $sum to calculate the total sales quantity per item.

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

Pipeline output:

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

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

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 the from 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 the coll field.

Unchanged behavior:

  • For a $lookup that starts with a stage for a collection, for example a $match or $collStats subpipeline, you must include the from field and specify the collection.

  • Similarly, for $unionWith, include the coll 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.

Back

$sortByCount