$unionWith (aggregation stage)

Definition

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

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 want 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 MongoDB Search `$search` stage as the first stage inside the pipeline. To learn more, see MongoDB Search Support.

The $unionWith operation would correspond to the following SQL statement:

SELECT *FROM Collection1WHERE ...UNION ALLSELECT *FROM Collection2WHERE ...

Considerations

Duplicate Results

The combined results from the previous stage and the $unionWith stage can include duplicates.

For example, the following aggregation combines the title field projection results from two queries on the movies collection. The result set contains duplicates:

db.movies.aggregate( [   { $match: { runtime: { $gt: 1000 } } },   { $project: { title: 1, _id: 0 } },   { $unionWith: { coll: "movies", pipeline: [      { $match: { runtime: { $gt: 1000 } } },      { $project: { title: 1, _id: 0 } }   ] } }] )

[  { title: 'Centennial' },  { title: 'Baseball' },  { title: 'Centennial' },  { title: 'Baseball' }]

To remove the duplicates, you can include a $group stage to group by the title field. The result set no longer contains duplicates:

db.movies.aggregate( [   { $match: { runtime: { $gt: 1000 } } },   { $project: { title: 1, _id: 0 } },   { $unionWith: { coll: "movies", pipeline: [      { $match: { runtime: { $gt: 1000 } } },      { $project: { title: 1, _id: 0 } }   ] } },   { $group: { _id: "$title" } },   { $sort: { _id: 1 } }] )

[  { _id: 'Baseball' },  { _id: 'Centennial' }]

Collation

If the db.collection.aggregate() includes a collation document, the operation uses that collation and ignores 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.

MongoDB Search Support

Starting in MongoDB 6.0, you can specify the MongoDB 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 MongoDB Search tutorial Run a MongoDB Search $search Query Using $unionWith.

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

Restrictions

Restrictions	Description
transactions	An aggregation pipeline cannot use `$unionWith` inside transactions.
`$out`	The $unionWith pipeline cannot include the `$out` stage.
`$merge`	The $unionWith pipeline cannot include the `$merge` stage.

Examples

The examples on this page use data from the sample_mflix dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the Sample Dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

Create Movie Reports from the Union of Movie Data

The following examples use the $unionWith stage to combine data and return results from the movies collection.

Report 1: All Movies by Category and Year

The following aggregation creates a movie report that lists movies by category. The pipeline uses $unionWith to combine documents from the movies collection:

db.movies.aggregate( [   { $match: { year: { $lt: 1910 } } },   { $set: { category: "Early Cinema" } },   { $unionWith: { coll: "movies", pipeline: [      { $match: { runtime: { $gt: 1000 } } },      { $set: { category: "Long Runtime" } }   ] } },   { $project: { title: 1, year: 1, category: 1, _id: 0 } },   { $sort: { category: 1, year: 1, title: 1 } }] )

[  { title: 'The Kiss', year: 1896, category: 'Early Cinema' },  { title: 'The Kiss', year: 1896, category: 'Early Cinema' },  { title: 'The Great Train Robbery', year: 1903, category: 'Early Cinema' },  { title: 'A Corner in Wheat', year: 1909, category: 'Early Cinema' },  { title: 'Centennial', year: 1978, category: 'Long Runtime' },  { title: 'Baseball', year: 1994, category: 'Long Runtime' }]

Specifically, the aggregation pipeline uses:

A $match stage to select movies released before 1910.
A $set stage to add a category field with the value Early Cinema.
A $unionWith stage to combine documents from the movies collection, filtered to movies with a runtime greater than 1000 and tagged with the Long Runtime category.
A $project stage to return only the title, year, and category fields.
A $sort stage to sort by the category, the year, and the title.

Report 2: Aggregated Runtime by Genre

The following aggregation creates a movie report that lists the total runtime per genre. The pipeline uses $unionWith to combine documents from the movies collection:

db.movies.aggregate( [   { $match: { year: { $lt: 1910 } } },   { $unionWith: { coll: "movies", pipeline: [      { $match: { runtime: { $gt: 1000 } } }   ] } },   { $group: { _id: "$genres", totalRuntime: { $sum: "$runtime" } } },   { $sort: { totalRuntime: -1 } }] )

[  { _id: [ 'Action', 'Adventure', 'Drama' ], totalRuntime: 1256 },  { _id: [ 'Documentary', 'History', 'Sport' ], totalRuntime: 1140 },  { _id: [ 'Short', 'Drama' ], totalRuntime: 14 },  { _id: [ 'Short', 'Western' ], totalRuntime: 11 },  { _id: [ 'Short', 'Romance' ], totalRuntime: 2 }]

The $match and $unionWith stages retrieve documents from the movies collection into the pipeline.
The $group stage groups by the genres field and uses $sum to calculate the total runtime per genres value.
The $sort stage orders the documents by descending totalRuntime.

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.

The following $unionWith operation performs a union with documents specified in the pipeline $documents field:

db.movies.aggregate( [   { $match: { runtime: { $gt: 1000 } } },   { $project: { title: 1, year: 1, _id: 0 } },   {      $unionWith: {         pipeline: [            {               $documents: [                  { title: "Movie A", year: 2000 },                  { title: "Movie B", year: 2001 }               ]            }         ]      }   }] )

[  { title: 'Centennial', year: 1978 },  { title: 'Baseball', year: 1994 },  { title: 'Movie A', year: 2000 },  { title: 'Movie B', year: 2001 }]

The C# examples on this page use the sample_mflix database from the Atlas sample datasets. To learn how to create a free MongoDB Atlas cluster and load the sample datasets, see Get Started in the MongoDB .NET/C# Driver documentation.

The following Movie class models the documents in the sample_mflix.movies collection:

[BsonIgnoreExtraElements]public class Movie{    [BsonId]    public ObjectId Id { get; set; }    [BsonElement("title")]    public string Title { get; set; } = null!;    [BsonElement("year")]    public int? Year { get; set; }    [BsonElement("runtime")]    public int? Runtime { get; set; }    [BsonElement("rated")]    public string? Rated { get; set; }    [BsonElement("metacritic")]    public int Metacritic { get; set; }    [BsonElement("plot")]    public string? Plot { get; set; }    [BsonElement("type")]    public string? Type { get; set; }    [BsonElement("cast")]    public string[]? Cast { get; set; }    [BsonElement("directors")]    public string[]? Directors { get; set; }    [BsonElement("writers")]    public string[]? Writers { get; set; }    [BsonElement("imdb")]    public ImdbData? Imdb { get; set; }}

To use the MongoDB .NET/C# driver to add a $unionWith stage to an aggregation pipeline, call the UnionWith() method on a PipelineDefinition object.

The following example creates a pipeline stage that combines movies with a Metacritic score of 100 from the sample_mflix.movies collection with movies matching the same criteria:

var pipeline = new EmptyPipelineDefinition<Movie>()    .Match(Builders<Movie>.Filter.Eq(m => m.Metacritic, 100))    .UnionWith(        withCollection: _movies,        withPipeline: new EmptyPipelineDefinition<Movie>()            .Match(Builders<Movie>.Filter.Eq(m => m.Metacritic, 100)));

{ "metacritic" : 100, "title" : "Best Kept Secret" }{ "metacritic" : 100, "title" : "Best Kept Secret" }{ "metacritic" : 100, "title" : "Boyhood" }{ "metacritic" : 100, "title" : "Boyhood" }{ "metacritic" : 100, "title" : "Fanny and Alexander" }{ "metacritic" : 100, "title" : "Fanny and Alexander" }{ "metacritic" : 100, "title" : "Lawrence of Arabia" }{ "metacritic" : 100, "title" : "Lawrence of Arabia" }{ "metacritic" : 100, "title" : "The Conformist" }{ "metacritic" : 100, "title" : "The Conformist" }{ "metacritic" : 100, "title" : "The Godfather" }{ "metacritic" : 100, "title" : "The Godfather" }{ "metacritic" : 100, "title" : "The Leopard" }{ "metacritic" : 100, "title" : "The Leopard" }{ "metacritic" : 100, "title" : "The Wizard of Oz" }{ "metacritic" : 100, "title" : "The Wizard of Oz" }

The Node.js examples on this page use the sample_mflix database from the Atlas sample datasets. To learn how to create a free MongoDB Atlas cluster and load the sample datasets, see Get Started in the MongoDB Node.js driver documentation.

To use the MongoDB Node.js driver to add a $unionWith stage to an aggregation pipeline, use the $unionWith operator in a pipeline object.

The following example creates a pipeline stage that combines the incoming documents from the sample_mflix.movies collection with the movie documents in the sample_mflix.movies collection. The example then runs the aggregation pipeline:

const db = client.db("sample_mflix");const collection = db.collection("movies");
const pipeline = [{ $unionWith: { coll: "Movies" } }];
const cursor = collection.aggregate(pipeline);return cursor;

Back

$sortByCount

$unset