Docs Menu
Docs Home
/
MongoDB Manual
/
Reference
/
Operators
/
Aggregation Pipeline Stages

$unionWith (aggregation)

On this page

  • Definition
  • Syntax
  • Considerations
  • Duplicates Results
  • $unionWith a Sharded Collection
  • Collation
  • Atlas Search Support
  • Restrictions
  • Examples
  • Create a Yearly Report from the Union of Quarterly Data Collections

Definition

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

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

Duplicates Results

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

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

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

Examples

Create a Yearly Report from the Union of Quarterly Data Collections

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

Report 1: All Sales by Quarter and Stores and Items

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 }

Report 2: Aggregated Yearly Sales by Items

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 }

Back

$sortByCount (aggregation)