Docs Menu

Docs Home → Develop Applications → 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

Definition

$unionWith

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.

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

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

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.

To see an example of $unionWith with $search, see the Atlas Search tutorial Run an Atlas Search $search Query Using $unionWith.

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

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

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

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

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

On this page