> For the complete MongoDB documentation index, see www.mongodb.com/docs/llms.txt

<!--
Tab options on this page. Append to the .md URL to filter:
  ?tabs=<id,...>   select specific tabs (e.g. ?tabs=nodejs,shell)
  ?allTabs=true    include every tab
  (no param)       default: one tab per tabset

Available tabs:
  other tabs: search, searchmeta
  drivers: shell, csharp, nodejs
-->

# $unionWith (aggregation stage)

## Definition

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

## Syntax

The `$unionWith` stage has the following syntax:

```javascript
{ $unionWith: { coll: "<collection>", pipeline: [ <stage1>, ... ] } }
```

To include all documents from the specified collection without any processing, you can use the simplified form:

```javascript
{ $unionWith: "<collection>" }  // Include all documents from the specified collection
```

The `$unionWith` stage takes a document with the following fields:

| Field | Necessity | Description |
| --- | --- | --- |
| [coll](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-coll) | Required if `pipeline` is omitted. Otherwise optional. | The collection or view whose [pipeline](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-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`.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/documents.md#mongodb-pipeline-pipe.-documents) |
| [pipeline](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-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.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-example-specified-docs) The pipeline cannot include the [`$out`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#mongodb-pipeline-pipe.-out) and [`$merge`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#mongodb-pipeline-pipe.-merge) stages. Starting in v6.0, the `pipeline` can contain the [MongoDB Search](https://www.mongodb.com/docs/atlas/atlas-search/) [`$search`](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search) stage as the first stage inside the pipeline. To learn more, see [MongoDB Search Support.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-fts-unionwith-behavior) |

The [`$unionWith`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) operation would correspond to the following SQL statement:

```sql
SELECT *
FROM Collection1
WHERE ...
UNION ALL
SELECT *
FROM Collection2
WHERE ...
```

## Considerations

### Duplicate Results

The combined results from the previous stage and the [`$unionWith`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) stage can include duplicates.

For example, create a `suppliers` collection and a `warehouses` collection:

```javascript
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" },
])
```

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

```javascript
db.suppliers.aggregate([
   { $project: { state: 1, _id: 0 } },
   { $unionWith: { coll: "warehouses", pipeline: [ { $project: { state: 1, _id: 0 } } ]} }
])
```

The result set contains duplicates:

```javascript
{ "state" : "Texas" }
{ "state" : "Colorado" }
{ "state" : "Rhode Island" }
{ "state" : "California" }
{ "state" : "Colorado" }
{ "state" : "Florida" }
```

To remove the duplicates, you can include a [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage to group by the `state` field:

```javascript
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:

```javascript
 { "_id" : "California" }
 { "_id" : "Texas" }
 { "_id" : "Florida" }
 { "_id" : "Colorado" }
 { "_id" : "Rhode Island" }
```

### Collation

If the [`db.collection.aggregate()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate.md#mongodb-method-db.collection.aggregate) includes a `collation` document, the operation uses that collation and ignores any other collations.

If the [`db.collection.aggregate()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate.md#mongodb-method-db.collection.aggregate) does not include a `collation` document, the [`db.collection.aggregate()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate.md#mongodb-method-db.collection.aggregate) method uses the collation for the top-level collection/view on which the [`db.collection.aggregate()`](https://www.mongodb.com/docs/manual/reference/method/db.collection.aggregate.md#mongodb-method-db.collection.aggregate) is run:

- If the [$unionWith coll](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-coll) is a collection, its collation is ignored.

- If the [$unionWith coll](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-coll) is a [view](https://www.mongodb.com/docs/manual/core/views.md), then its collation must match that of the top-level collection/view.  Otherwise, the operation errors.

### MongoDB Search Support

Starting in MongoDB 6.0, you can specify the [MongoDB Search](https://www.mongodb.com/docs/atlas/atlas-search/) [`$search`](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search) or [`$searchMeta`](https://www.mongodb.com/docs/search/query/aggregation-stages/searchMeta.md#mongodb-pipeline-pipe.-searchMeta) stage in the `$unionWith` pipeline to search collections on the Atlas cluster. The [`$search`](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search) or the [`$searchMeta`](https://www.mongodb.com/docs/search/query/aggregation-stages/searchMeta.md#mongodb-pipeline-pipe.-searchMeta) stage must be the first stage inside the `$unionWith` pipeline.

### $search

```text
[{
  "$unionWith": {
    "coll": <collection-name>,
    "pipeline": [{
      "$search": {
        "<operator>": {
          <operator-specification>
        }
      },
      ...
    }]
  }
}]
```

To see an example of [`$unionWith`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) with [`$search`](https://www.mongodb.com/docs/search/query/aggregation-stages/search.md#mongodb-pipeline-pipe.-search), see the MongoDB Search tutorial [Run a MongoDB Search $search Query Using $unionWith.](https://www.mongodb.com/docs/atlas/atlas-search/tutorial/search-with-unionwith/)

### Restrictions

| Restrictions | Description |
| --- | --- |
| [transactions](https://www.mongodb.com/docs/manual/core/transactions.md#std-label-transactions) | An aggregation pipeline cannot use [`$unionWith`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) inside [transactions.](https://www.mongodb.com/docs/manual/core/transactions.md#std-label-transactions) |
| [`$out`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#mongodb-pipeline-pipe.-out) | The [$unionWith pipeline](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-pipeline) cannot include the [`$out`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/out.md#mongodb-pipeline-pipe.-out) stage. |
| [`$merge`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#mongodb-pipeline-pipe.-merge) | The [$unionWith pipeline](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-pipeline) cannot include the [`$merge`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge.md#mongodb-pipeline-pipe.-merge) stage. |

## Examples

### MongoDB Shell

### Create Sales Reports from the Union of Yearly Data Collections

The following examples use the [`$unionWith`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-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:

   ```javascript
   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:

   ```javascript
   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:

   ```javascript
   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:

   ```javascript
   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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) to combine documents from all four collections:

```javascript
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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/set.md#mongodb-pipeline-pipe.-set) stage to update the `_id` field to contain the year.

- A sequence of [`$unionWith`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) stages to combine all documents from the four collections, each also using the [`$set`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/set.md#mongodb-pipeline-pipe.-set) stage on its documents.

- A [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort) stage to sort by the `_id` (the year), the `store`, and `item`.

Pipeline output:

```javascript
{ "_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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) to combine documents from all four years:

```javascript
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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#mongodb-pipeline-pipe.-unionWith) stages retrieve documents from the specified collections into the pipeline:

- The [`$group`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/group.md#mongodb-pipeline-pipe.-group) stage groups by the `item` field and uses [`$sum`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sum.md#mongodb-group-grp.-sum) to calculate the total sales quantity per `item`.

- The [`$sort`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/sort.md#mongodb-pipeline-pipe.-sort) stage orders the documents by descending `total`.

Pipeline output:

```javascript
{ "_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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/documents.md#mongodb-pipeline-pipe.-documents) stage in the `pipeline` field, you perform a union with documents that aren't stored in a separate collection.

Create a collection `cakeFlavors`:

```javascript
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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/documents.md#mongodb-pipeline-pipe.-documents) field:

```javascript
db.cakeFlavors.aggregate( [
   {
      $unionWith: {
         pipeline: [
            {
               $documents: [
                  { _id: 4, flavor: "orange" },
                  { _id: 5, flavor: "vanilla", price: 20 }
               ]
            }
         ]
      }
   }
] )
```

Output:

```javascript
[
   { _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 the `from` field if you use a subpipeline with a stage which doesn't require a specified collection. For example, a [`$documents`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/documents.md#mongodb-pipeline-pipe.-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`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/match.md#mongodb-pipeline-pipe.-match) or [`$collStats`](https://www.mongodb.com/docs/manual/reference/operator/aggregation/collstats.md#mongodb-pipeline-pipe.-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`:

```javascript
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:

```javascript
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.](https://www.mongodb.com/docs/manual/reference/operator/aggregation/unionwith.md#std-label-unionWith-duplicate-results)