Docs Menu

Docs HomeDevelop ApplicationsMongoDB 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
$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.

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.

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:

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

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.

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 }
←  $sortByCount (aggregation)$unset (aggregation) →