======================== $unionWith (aggregation) ======================== .. default-domain:: mongodb .. contents:: On this page :local: :backlinks: none :depth: 1 :class: singlecol Definition ---------- .. pipeline:: $unionWith .. versionadded:: 4.4 Performs a union of two collections; i.e. :pipeline:`$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. Syntax ------ The :pipeline:`$unionWith` stage has the following syntax: .. code-block:: javascript { $unionWith: { coll: "", pipeline: [ , ... ] } } To include all documents from the specified collection without any processing, you can use the simplified form: .. code-block:: javascript { $unionWith: "" } // Include all documents from the specified collection The :pipeline:`$unionWith` stage takes a document with the following fields: .. list-table:: :header-rows: 1 :widths: 15 85 * - Field - Description * - :ref:`coll ` - .. _unionWith-coll: The collection or view whose :ref:`pipeline ` results you wish to include in the result set. * - :ref:`pipeline ` - .. _unionWith-pipeline: Optional. An aggregation pipeline to apply to the specified :ref:`coll `. ``[ , , ...]`` The pipeline cannot include the :pipeline:`$out` and :pipeline:`$merge` stages. The :pipeline:`$unionWith` operation would correspond to the following SQL statement: .. code-block:: sql SELECT * FROM Collection1 WHERE ... UNION ALL SELECT * FROM Collection2 WHERE ... Considerations -------------- Duplicates Results ~~~~~~~~~~~~~~~~~~ The combined results from the previous stage and the :pipeline:`$unionWith` stage can include duplicates. For example, create a suppliers collection: .. code-block:: 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" }, ]) .. code-block:: 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 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: .. code-block:: javascript 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: .. code-block:: javascript :emphasize-lines: 2,5 { "state" : "Texas" } { "state" : "Colorado" } { "state" : "Rhode Island" } { "state" : "California" } { "state" : "Colorado" } { "state" : "Florida" } To remove the duplicates, you can include a :pipeline:`$group` stage to group by the ``state`` field: .. code-block:: 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: .. code-block:: javascript { "_id" : "California" } { "_id" : "Texas" } { "_id" : "Florida" } { "_id" : "Colorado" } { "_id" : "Rhode Island" } ``$unionWith`` a Sharded Collection ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If the :pipeline:`$unionWith` stage is part of the :ref:`$lookup pipeline `, the ``$unionWith`` :ref:`coll ` cannot be sharded. For example, in the following aggregation operation, the ``inventory_q1`` collection cannot be sharded: .. code-block:: javascript :copyable: false :emphasize-lines: 8 db.suppliers.aggregate([ { $lookup: { from: "warehouses", let: { order_item: "$item", order_qty: "$ordered" }, pipeline: [ ... { $unionWith: { coll: "inventory_q1", pipeline: [ ... ] } }, ... ], as: "stockdata" } } ]) Collation ~~~~~~~~~ If the :method:`db.collection.aggregate()` includes a :ref:`collation `, that collation is used for the operation, ignoring any other collations. If the :method:`db.collection.aggregate()` does not include a :ref:`collation `, the :method:`db.collection.aggregate()` method uses the collation for the top-level collection/view on which the :method:`db.collection.aggregate()` is run: - If the :ref:`$unionWith coll ` is a collection, its collation is ignored. - If the :ref:`$unionWith coll ` is a :doc:`view `, then its collation must match that of the top-level collection/view. Otherwise, the operation errors. Restrictions ~~~~~~~~~~~~ .. list-table:: :header-rows: 1 :widths: 30 70 * - Restrictions - Description * - :doc:`Transactions ` - An aggregation pipeline cannot use :pipeline:`$unionWith` inside :doc:`transactions `. * - Sharded Collection - If the :pipeline:`$unionWith` stage is part of the :ref:`$lookup pipeline `, the :ref:`$unionWith coll ` cannot be sharded. * - :pipeline:`$out` - The :ref:`$unionWith pipeline ` cannot include the :pipeline:`$out` stage. * - :pipeline:`$merge` - The :ref:`$unionWith pipeline ` cannot include the :pipeline:`$merge` stage. Examples -------- Create a Yearly Report from the Union of Quarterly Data Collections ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Create a sample ``sales2019q1`` collection with the following documents: .. code-block:: javascript 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: .. code-block:: javascript 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: .. code-block:: javascript 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: .. code-block:: javascript 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 :pipeline:`$unionWith` to combine documents from all four collections to create a yearly sales report that lists all sales by quarter and stores: .. code-block:: javascript 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 :pipeline:`$set` stage to update the ``_id`` field to contain the quarter. That is, the documents from this stage has the form: .. code-block:: javascript :copyable: false { "_id" : "2019Q1", "store" : "A", "item" : "Chocolates", "quantity" : 150 } { "_id" : "2019Q1", "store" : "B", "item" : "Chocolates", "quantity" : 50 } ... - a sequence of :pipeline:`$unionWith` stages to combine all documents from the four collections; each also using the :pipeline:`$set` stage on its documents. That is, the documents are from all four collections and have the form: .. code-block:: javascript :copyable: false { "_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 :pipeline:`$sort` stage to sort by the ``_id`` (i.e. the quarter), the ``store``, and ``item``. .. code-block:: javascript :copyable: false { "_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 :pipeline:`$unionWith` to combine documents from all four collections to create a yearly sales report that lists the yearly sales quantity per item: .. code-block:: javascript db.sales2019q1.aggregate( [ { $unionWith: "sales2019q2" }, { $unionWith: "sales2019q3" }, { $unionWith: "sales2019q4" }, { $group: { _id: "$item", total: { $sum: "$quantity" } } }, { $sort: { total: -1 }} ] ) - The sequence of :pipeline:`$unionWith` stages retrieve documents from the specified collections into the pipeline: .. code-block:: javascript :copyable: false { "_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 :pipeline:`$group` stage groups by the ``item`` field and uses :group:`$sum` to calculate the yearly total sales quantity per ``item``: .. code-block:: javascript :copyable: false { "_id" : "Cookies", "total" : 1720 } { "_id" : "Pie", "total" : 395 } { "_id" : "Cheese", "total" : 350 } { "_id" : "Chocolates", "total" : 1250 } { "_id" : "Nuts", "total" : 510 } - The :pipeline:`$sort` stage orders the documents by descending ``total``. .. code-block:: javascript :copyable: false { "_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 :pipeline:`$group` stage within each :pipeline:`$unionWith` stage: .. code-block:: javascript 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 :pipeline:`$group` groups the 2019q1 sales totals by items: .. code-block:: javascript :copyable: false { "_id" : "Pie", "total" : 30 } { "_id" : "Cookies", "total" : 440 } { "_id" : "Chocolates", "total" : 400 } - The sequence of :pipeline:`$unionWith` stages groups the sales total by the items from the specified collections into the pipeline: .. code-block:: javascript :copyable: false { "_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 :pipeline:`$group` stage groups these quarterly groupings: .. code-block:: javascript :copyable: false { "_id" : "Cookies", "total" : 1720 } { "_id" : "Pie", "total" : 395 } { "_id" : "Cheese", "total" : 350 } { "_id" : "Chocolates", "total" : 1250 } { "_id" : "Nuts", "total" : 510 } - The :pipeline:`$sort` stage orders the documents by descending ``total``. .. code-block:: javascript :copyable: false { "_id" : "Cookies", "total" : 1940 } { "_id" : "Chocolates", "total" : 1450 } { "_id" : "Nuts", "total" : 510 } { "_id" : "Pie", "total" : 410 } { "_id" : "Cheese", "total" : 350 }