.. _agg-merge: ==================== $merge (aggregation) ==================== .. default-domain:: mongodb .. contents:: On this page :local: :backlinks: none :depth: 1 :class: twocols .. meta:: :description: MongoDB on-demand materialized view, SELECT INTO :keywords: MongoDB, materialized view, SELECT INTO, INSERT INTO Definition ---------- .. note:: The following page discusses the :pipeline:`$merge` stage, which outputs the aggregation pipeline results to a collection. For a discussion of the :expression:`$mergeObjects` operator which merges documents into a single document, see :expression:`$mergeObjects` instead. .. pipeline:: $merge Writes the results of the :doc:`aggregation pipeline ` to a specified collection. The :pipeline:`$merge` operator must be the **last** stage in the pipeline. The :pipeline:`$merge` stage: - Can output to a collection in the same or different database. - Creates a new collection if the output collection does not already exist. - Can incorporate results (insert new documents, merge documents, replace documents, keep existing documents, fail the operation, process documents with a custom update pipeline) into an existing collection. - Can output to a sharded collection. Input collection can also be sharded. For a comparison with the :pipeline:`$out` stage which also outputs the aggregation results to a collection, see :ref:`merge-out-comparison`. .. topic:: On-Demand Materialized Views :pipeline:`$merge` can incorporate the pipeline results into an existing output collection rather than perform a full replacement of the collection. This functionality allows users to create on-demand materialized views, where the content of the output collection is incrementally updated when the pipeline is run. For more information on this use case, see :doc:`/core/materialized-views` as well as the examples on this page. Materialized views are separate from read-only views. For information on creating read-only views, see :doc:`read-only views `. Syntax ------ :pipeline:`$merge` has the following syntax: .. code-block:: javascript { $merge: { into: -or- { db: , coll: }, on: -or- [ , ...], // Optional let: , // Optional whenMatched: , // Optional whenNotMatched: // Optional } } For example: .. code-block:: javascript { $merge: { into: "myOutput", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } } If using all default options for :pipeline:`$merge` (including writing to a collection in the same database), you can use the simplified form: .. code-block:: javascript { $merge: } // Output collection is in the same database The :pipeline:`$merge` takes a document with the following fields: .. list-table:: :header-rows: 1 :widths: 17 83 * - Field - Description * - :ref:`into ` - .. _merge-into: The output collection. Specify either: - The collection name as a string to output to a collection in the same database where the aggregation is run. For example: | ``into: "myOutput"`` | - The database and collection name in a document to output to a collection in the specified database. For example: | ``into: { db:"myDB", coll:"myOutput" }`` .. note:: - If the output collection does not exist, :pipeline:`$merge` creates the collection: - For a :ref:`replica set ` or a standalone, if the output database does not exist, :pipeline:`$merge` also creates the database. - For a :ref:`sharded cluster `, the specified output database must already exist. - The output collection cannot be the same collection as the collection being aggregated. - The output collection cannot appear in any other stages of the pipeline. - The output collection can be a sharded collection. * - :ref:`on ` - .. _merge-on: Optional. Field or fields that act as a unique identifier for a document. The identifier determines if a results document :ref:`matches ` an already existing document in the output collection. Specify either: - A single field name as a string. For example: | ``on: "_id"`` | - A combination of fields in an array. For example: | ``on: [ "date", "customerId" ]`` | The order of the fields in the array does not matter, and you cannot specify the same field multiple times. For the specified field or fields: - The aggregation results documents must contain the field(s) specified in the ``on``, unless the ``on`` field is the ``_id`` field. If the ``_id`` field is missing from a results document, MongoDB adds it automatically. - The specified field or fields cannot contain a null or an array value. :pipeline:`$merge` requires a :ref:`unique `, index with keys that correspond to the :ref:`on ` identifier fields. Although the order of the index key specification does not matter, the unique index must only contain the ``on`` fields as its keys. - The index must also have the same :doc:`collation ` as the aggregation's collation. - The unique index can be a :ref:`sparse ` index. - For output collections that already exist, the corresponding index must already exist. The default value for :ref:`on ` depends on the output collection: - If the output collection does not exist, the :ref:`on ` identifier must be and defaults to the ``_id`` field. The corresponding unique ``_id`` index is automatically created. .. tip:: To use a different :ref:`on ` identifier field(s) for a collection that does not exist, you can create the collection first by creating a unique index on the desired field(s). See the :ref:`section on non-existent output collection ` for an example. - If the existing output collection is unsharded, the ``on`` identifier defaults to the ``_id`` field. - If the existing output collection is a sharded collection, the :ref:`on ` identifier defaults to all the shard key fields and the ``_id`` field. If specifying a different ``on`` identifier, the ``on`` must contain all the shard key fields. * - :ref:`whenMatched ` - .. _merge-whenMatched: Optional. The behavior of :pipeline:`$merge` if a result document and an existing document in the collection have the same value for the specified :ref:`on ` field(s). You can specify either: - One of the pre-defined action strings: .. list-table:: :header-rows: 1 :widths: 20 80 * - Action - Description * - :ref:`"replace" ` - .. _merge-whenMatched-replace: Replace the existing document in the :ref:`output collection ` with the matching results document. When performing a replace, the replacement document cannot result in a modification of the ``_id`` value or, if the output collection is sharded, the shard key value. Otherwise, the operation results in an error. .. tip:: To avoid this error, if the :ref:`on ` field does not include the ``_id`` field, remove the ``_id`` field in the aggregation results to avoid the error, such as with a preceding :pipeline:`$unset` stage, etc. * - :ref:`"keepExisting" ` - .. _merge-whenMatched-keepExisting: Keep the existing document in the :ref:`output collection `. * - :ref:`"merge" ` (Default) - .. _merge-whenMatched-merge: Merge the matching documents (similar to the :expression:`$mergeObjects` operator). - If the results document contains fields not in the existing document, add these new fields to the existing document. - If the results document contains fields in the existing document, replace the existing field values with those from the results document. For example, if the output collection has the document: ``{ _id: 1, a: 1, b: 1 }`` And the aggregation results has the document: ``{ _id: 1, b: 5, z: 1 }`` Then, the merged document is: ``{ _id: 1, a: 1, b: 5, z: 1 }`` When performing a merge, the merged document cannot result in a modification of the ``_id`` value or, if the output collection is sharded, the shard key value. Otherwise, the operation results in an error. .. tip:: To avoid this error, if the :ref:`on ` field does not include the ``_id`` field, remove the ``_id`` field in the aggregation results to avoid the error, such as with a preceding :pipeline:`$unset` stage, etc. * - :ref:`"fail" ` - .. _merge-whenMatched-fail: Stop and fail the aggregation operation. Any changes to the output collection from previous documents are not reverted. - .. _merge-whenMatched-pipeline: An aggregation pipeline to update the document in the collection. ``[ , ... ]`` The pipeline can only consist of the following stages: - :pipeline:`$addFields` and its alias :pipeline:`$set` - :pipeline:`$project` and its alias :pipeline:`$unset` - :pipeline:`$replaceRoot` and its alias :pipeline:`$replaceWith` The pipeline cannot modify the :ref:`on ` field's value. For example, if you are matching on the field ``month``, the pipeline cannot modify the ``month`` field. The whenMatched ``pipeline`` can directly access the fields of the existing documents in the **output** collection using ``$``. To access the fields from the aggregation results documents, use either: - The built-in ``$$new`` variable to access the field, i.e. ``$$new.``. The ``$$new`` variable is only available if the :ref:`let ` specification is omitted. .. include:: /includes/extracts/4.2-changes-new-variable-reserved.rst - The user-defined variables in the :ref:`let ` field, i.e. ``$$.``. * - :ref:`let ` - .. _merge-let: Optional. Specifies variables accessible for use in the :ref:`whenMatched pipeline ` Specify a document with the variable name and value expression: ``{ : , ..., : }`` If unspecified, defaults to ``{ new: "$$ROOT" }``; i.e. the :ref:`whenMatched pipeline ` can access the ``$$new`` variable. .. include:: /includes/extracts/4.2-changes-new-variable-reserved.rst To access the ``let`` variables in the :ref:`whenMatched pipeline `, use the double dollar signs ($$) prefix and variable name ``$$``. * - :ref:`whenNotMatched ` - .. _merge-whenNotMatched: Optional. The behavior of :pipeline:`$merge` if a result document does not match an existing document in the out collection. You can specify one of the pre-defined action strings: .. list-table:: :header-rows: 1 :widths: 20 80 * - Action - Description * - :ref:`"insert" ` (Default) - .. _merge-whenNotMatched-insert: Insert the document into the output collection. * - :ref:`"discard" ` - .. _merge-whenNotMatched-discard: Discard the document; i.e. :pipeline:`$merge` does not insert the document into the output collection. * - :ref:`"fail" ` - .. _merge-whenNotMatched-fail: Stop and fail the aggregation operation. Any changes already written to the output collection are not reverted. .. _aggregation-merge-behavior: Considerations -------------- ``_id`` Field Generation ~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: If the ``_id`` field is not present in a document from the aggregation pipeline results, the :pipeline:`$merge` stage generates it automatically. For example, in the following aggregation pipeline, :pipeline:`$project` excludes the ``_id`` field from the documents passed into :pipeline:`$merge`. When :pipeline:`$merge` writes these documents to the ``"newCollection"``, :pipeline:`$merge` generates a new ``_id`` field and value. .. code-block:: javascript db.sales.aggregate( [ { $project: { _id: 0 } }, { $merge : { into : "newCollection" } } ] ) .. _merge-behavior-create-collection: Create a New Collection if Output Collection is Non-Existent ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: The :pipeline:`$merge` operation creates a new collection if the specified output collection does not exist. - The output collection is created when :pipeline:`$merge` writes the first document into the collection and is immediately visible. - If the aggregation fails, any writes completed by the :pipeline:`$merge` before the error will not be rolled back. .. note:: For a :ref:`replica set ` or a standalone, if the output database does not exist, :pipeline:`$merge` also creates the database. For a :ref:`sharded cluster `, the specified output database must already exist. .. _merge-use-different-on: If the output collection does not exist, :pipeline:`$merge` requires the :ref:`on ` identifier to be the ``_id`` field. To use a different ``on`` field value for a collection that does not exist, you can create the collection first by creating a unique index on the desired field(s) first. For example, if the output collection ``newDailySales201905`` does not exist and you want to specify the ``salesDate`` field as the :ref:`on ` identifier: .. code-block:: javascript db.newDailySales201905.createIndex( { salesDate: 1 }, { unique: true } ) db.sales.aggregate( [ { $match: { date: { $gte: new Date("2019-05-01"), $lt: new Date("2019-06-01") } } }, { $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } }, totalqty: { $sum: "$quantity" } } }, { $project: { _id: 0, salesDate: { $toDate: "$_id" }, totalqty: 1 } }, { $merge : { into : "newDailySales201905", on: "salesDate" } } ] ) Output to a Sharded Collection ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: The :pipeline:`$merge` stage can output to a sharded collection. When the output collection is sharded, :pipeline:`$merge` uses the ``_id`` field and all the :ref:`shard key ` fields as the default :ref:`on ` identifier. If you override the default, the :ref:`on ` identifier must include all the :ref:`shard key ` fields: .. code-block:: javascript { $merge: { into: "" or { db:"", coll: "" }, on: [ "", "",... ], // Shard key fields and any additional fields let: , // Optional whenMatched: , // Optional whenNotMatched: // Optional } } For example, in a database that has :method:`sharding enabled `, use the :method:`sh.shardCollection()` method to create a new sharded collection ``newrestaurants`` with the ``postcode`` field as the shard key. .. code-block:: javascript sh.enableSharding("exampledb"); // Sharding must be enabled in the database sh.shardCollection( "exampledb.newrestaurants", // Namespace of the collection to shard { postcode: 1 }, // Shard key ); The ``newrestaurants`` collection will contain documents with information on new restaurant openings by month (``date`` field) and postcode (shard key); specifically, the :ref:`on ` identifier is ``["date", "postcode"]`` (the ordering of the fields does not matter). Because :pipeline:`$merge` requires a unique, index with keys that correspond to the :ref:`on ` identifier fields, create the unique index (the ordering of the fields do not matter): [#uniqueoption]_ .. code-block:: javascript use exampledb db.newrestaurants.createIndex( { postcode: 1, date: 1 }, { unique: true } ) With the sharded collection ``restaurants`` and the unique index created, you can use :pipeline:`$merge` to output the aggregation results to this collection, matching on ``[ "date", "postcode" ]`` as in the following example: .. code-block:: javascript use exampledb db.openings.aggregate([ { $group: { _id: { date: { $dateToString: { format: "%Y-%m", date: "$date" } }, postcode: "$postcode" }, restaurants: { $push: "$restaurantName" } } }, { $project: { _id: 0, postcode: "$_id.postcode", date: "$_id.date", restaurants: 1 } }, { $merge: { into: "newrestaurants", "on": [ "date", "postcode" ], whenMatched: "replace", whenNotMatched: "insert" } } ]) .. [#uniqueoption] The :method:`sh.shardCollection()` method can also create a unique index on the shard key when passed the ``{ unique: true }`` option if: the shard key is :doc:`range-based `; the collection is empty; and a unique index on the shard key doesn't already exist. In the example above, because the ``on`` identifier is the shard key and another field, a separate operation to create the corresponding index is required. .. _merge-behavior-replaceCollection: Replace Documents (``$merge``) vs Replace Collection (``$out``) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: :pipeline:`$merge` can replace an existing document in the output collection if the aggregation results contain a document or documents that match based on the :ref:`on ` specification. As such, :pipeline:`$merge` can replace all documents in the existing collection if the aggregation results include matching documents for all existing documents in the collection and you specify :ref:`"replace" ` for :ref:`whenMatched `. However, **to replace an existing collection** regardless of the aggregation results, use :pipeline:`$out` instead. Existing Documents and ``_id`` and Shard Key Values ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: The :pipeline:`$merge` errors if the :pipeline:`$merge` results in a change to an existing document's ``_id`` value. .. tip:: To avoid this error, if the :ref:`on ` field does not include the ``_id`` field, remove the ``_id`` field in the aggregation results to avoid the error, such as with a preceding :pipeline:`$unset` stage, etc. Additionally, for a sharded collection, :pipeline:`$merge` also errors if it results in a change to the shard key value of an exising document. Any writes completed by the :pipeline:`$merge` before the error will not be rolled back. .. _merge-unique-index-constraints: Unique Index Constraints ~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: If the unique index used by :pipeline:`$merge` for :ref:`on ` field(s) is dropped mid-aggregation, there is no guarantee that the aggregation will be killed. If the aggregation continues, there is no guarantee that documents do not have duplicate ``on`` field values. If the :pipeline:`$merge` attempts to write a document that violates any unique index on the output collection, the operation errors; for example: - :ref:`Insert a non-matching document ` that violates a unique index other than the index on the :ref:`on ` field(s). - :ref:`Fail ` if there is a matching document in the collection. Specifically, the operation attempts to insert the matching document which violates the unique index on the :ref:`on ` field(s). - :ref:`Replace an existing document ` with a new document that violates a unique index other than the index on the :ref:`on ` field(s). - :ref:`Merge the matching documents ` that results in a document that violates a unique index other than the index on the :ref:`on ` field(s). ``whenMatched`` Pipeline Behavior ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: Starting in MongoDB 4.2.2, if all of the following are true for a :pipeline:`$merge` stage: - The value of :ref:`whenMatched ` is an aggregation pipeline, - The value of :ref:`whenNotMatched ` is ``insert``, and - There is no match for a document in the output collection, :pipeline:`$merge` inserts the document directly into the output collection. Prior to MongoDB 4.2.2, when these conditions for a :pipeline:`$merge` stage are met, the pipeline specified in the :ref:`whenMatched ` field is executed with an empty input document. The resulting document from the pipeline is inserted into the output collection. .. seealso:: :ref:`4.2.2 Release Notes <4.2.2-release-notes>` .. _merge-out-comparison: Comparison with ``$out`` ~~~~~~~~~~~~~~~~~~~~~~~~ .. topic:: ``$merge`` and ``$out`` Comparison With the introduction of :pipeline:`$merge`, MongoDB provides two stages, :pipeline:`$merge` and :pipeline:`$out`, for writing the results of the aggregation pipeline to a collection. The following summarizes the capabilities of the two stages: .. list-table:: :header-rows: 1 :class: border-table * - :pipeline:`$merge` - :pipeline:`$out` * - - Can output to a collection in the same or different database. - - Can output to a collection in the same database only. * - - Creates a new collection if the output collection does not already exist. - - Creates a new collection if the output collection does not already exist. * - - Can incorporate results (insert new documents, merge documents, replace documents, keep existing documents, fail the operation, process documents with a custom update pipeline) into an existing collection. | See also :ref:`merge-behavior-replaceCollection`. - - Replaces the output collection completely if it already exists. * - - Can output to a sharded collection. Input collection can also be sharded. - - Cannot output to a sharded collection. Input collection, however, can be sharded. * - - Corresponds to SQL statements: - ``MERGE``. - ``INSERT INTO T2 SELECT FROM T1``. - ``SELECT INTO T2 FROM T1``. - Create/Refresh Materialized Views. - - Corresponds to SQL statement: - ``INSERT INTO T2 SELECT FROM T1``. - ``SELECT INTO T2 FROM T1``. Restrictions ------------ .. list-table:: :header-rows: 1 :widths: 30 70 * - Restrictions - Description * - Output Collection - - The output collection cannot be the same collection as the collection being aggregated. - The output collection cannot be a collection that appears in any other stages of the pipeline. For example, the aggregation pipeline cannot contain a :pipeline:`$lookup` stage or a :pipeline:`$graphLookup` stage that refers to the output collection. * - :doc:`Transactions ` - An aggregation pipeline cannot use :pipeline:`$merge` inside a :ref:`transaction `. * - | :doc:`View definition ` | *Separate from materialized view* - :doc:`View definition ` cannot include the :pipeline:`$merge` stage. If the view definition includes nested pipeline (e.g. the view definition includes :pipeline:`$facet` stage), this :pipeline:`$merge` stage restriction applies to the nested pipelines as well. * - :pipeline:`$lookup` stage - :pipeline:`$lookup` stage's :ref:`nested pipeline ` cannot include the :pipeline:`$merge` stage. * - :pipeline:`$facet` stage - :pipeline:`$facet` stage's :ref:`nested pipeline ` cannot include the :pipeline:`$merge` stage. * - :readconcern:`"linearizable"` read concern - .. include:: /includes/extracts/4.2-changes-linearizable-merge-restriction.rst .. _merge-examples: Examples -------- .. _merge-mat-view-init-creation: On-Demand Materialized View: Initial Creation ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: If the output collection does not exist, the :pipeline:`$merge` creates the collection. For example, a collection named ``salaries`` in the ``zoo`` database is populated with the employee salary and department history: .. code-block:: javascript db.getSiblingDB("zoo").salaries.insertMany([ { "_id" : 1, employee: "Ant", dept: "A", salary: 100000, fiscal_year: 2017 }, { "_id" : 2, employee: "Bee", dept: "A", salary: 120000, fiscal_year: 2017 }, { "_id" : 3, employee: "Cat", dept: "Z", salary: 115000, fiscal_year: 2017 }, { "_id" : 4, employee: "Ant", dept: "A", salary: 115000, fiscal_year: 2018 }, { "_id" : 5, employee: "Bee", dept: "Z", salary: 145000, fiscal_year: 2018 }, { "_id" : 6, employee: "Cat", dept: "Z", salary: 135000, fiscal_year: 2018 }, { "_id" : 7, employee: "Gecko", dept: "A", salary: 100000, fiscal_year: 2018 }, { "_id" : 8, employee: "Ant", dept: "A", salary: 125000, fiscal_year: 2019 }, { "_id" : 9, employee: "Bee", dept: "Z", salary: 160000, fiscal_year: 2019 }, { "_id" : 10, employee: "Cat", dept: "Z", salary: 150000, fiscal_year: 2019 } ]) You can use the :pipeline:`$group` and :pipeline:`$merge` stages to initially create a collection named ``budgets`` (in the ``reporting`` database) from the data currently in the ``salaries`` collection: .. note:: For a replica set or a standalone deployment, if the output database does not exist, :pipeline:`$merge` also creates the database. For a sharded cluster deployment, the specified output database must already exist. .. code-block:: javascript db.getSiblingDB("zoo").salaries.aggregate( [ { $group: { _id: { fiscal_year: "$fiscal_year", dept: "$dept" }, salaries: { $sum: "$salary" } } }, { $merge : { into: { db: "reporting", coll: "budgets" }, on: "_id", whenMatched: "replace", whenNotMatched: "insert" } } ] ) - :pipeline:`$group` stage to group the salaries by the ``fiscal_year`` and ``dept``. - :pipeline:`$merge` stage writes the output of the preceding :pipeline:`$group` stage to the ``budgets`` collection in the ``reporting`` database. To view the documents in the new ``budgets`` collection: .. code-block:: javascript db.getSiblingDB("reporting").budgets.find().sort( { _id: 1 } ) The ``budgets`` collection contains the following documents: .. code-block:: javascript { "_id" : { "fiscal_year" : 2017, "dept" : "A" }, "salaries" : 220000 } { "_id" : { "fiscal_year" : 2017, "dept" : "Z" }, "salaries" : 115000 } { "_id" : { "fiscal_year" : 2018, "dept" : "A" }, "salaries" : 215000 } { "_id" : { "fiscal_year" : 2018, "dept" : "Z" }, "salaries" : 280000 } { "_id" : { "fiscal_year" : 2019, "dept" : "A" }, "salaries" : 125000 } { "_id" : { "fiscal_year" : 2019, "dept" : "Z" }, "salaries" : 310000 } .. seealso:: :doc:`/core/materialized-views` .. _merge-mat-view-refresh: On-Demand Materialized View: Update/Replace Data ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: The following example refers to the collections from the previous example. The example ``salaries`` collection contains the employee salary and department history: .. code-block:: javascript { "_id" : 1, employee: "Ant", dept: "A", salary: 100000, fiscal_year: 2017 }, { "_id" : 2, employee: "Bee", dept: "A", salary: 120000, fiscal_year: 2017 }, { "_id" : 3, employee: "Cat", dept: "Z", salary: 115000, fiscal_year: 2017 }, { "_id" : 4, employee: "Ant", dept: "A", salary: 115000, fiscal_year: 2018 }, { "_id" : 5, employee: "Bee", dept: "Z", salary: 145000, fiscal_year: 2018 }, { "_id" : 6, employee: "Cat", dept: "Z", salary: 135000, fiscal_year: 2018 }, { "_id" : 7, employee: "Gecko", dept: "A", salary: 100000, fiscal_year: 2018 }, { "_id" : 8, employee: "Ant", dept: "A", salary: 125000, fiscal_year: 2019 }, { "_id" : 9, employee: "Bee", dept: "Z", salary: 160000, fiscal_year: 2019 }, { "_id" : 10, employee: "Cat", dept: "Z", salary: 150000, fiscal_year: 2019 } The example ``budgets`` collection contains the cumulative yearly budgets: .. code-block:: javascript { "_id" : { "fiscal_year" : 2017, "dept" : "A" }, "salaries" : 220000 } { "_id" : { "fiscal_year" : 2017, "dept" : "Z" }, "salaries" : 115000 } { "_id" : { "fiscal_year" : 2018, "dept" : "A" }, "salaries" : 215000 } { "_id" : { "fiscal_year" : 2018, "dept" : "Z" }, "salaries" : 280000 } { "_id" : { "fiscal_year" : 2019, "dept" : "A" }, "salaries" : 125000 } { "_id" : { "fiscal_year" : 2019, "dept" : "Z" }, "salaries" : 310000 } During the current fiscal year (``2019`` in this example), new employees are added to the ``salaries`` collection and new head counts are pre-allocated for the next year: .. code-block:: javascript db.getSiblingDB("zoo").salaries.insertMany([ { "_id" : 11, employee: "Wren", dept: "Z", salary: 100000, fiscal_year: 2019 }, { "_id" : 12, employee: "Zebra", dept: "A", salary: 150000, fiscal_year: 2019 }, { "_id" : 13, employee: "headcount1", dept: "Z", salary: 120000, fiscal_year: 2020 }, { "_id" : 14, employee: "headcount2", dept: "Z", salary: 120000, fiscal_year: 2020 } ]) To update the ``budgets`` collection to reflect the new salary information, the following aggregation pipeline uses: - :pipeline:`$match` stage to find all documents with ``fiscal_year`` greater than or equal to ``2019``. - :pipeline:`$group` stage to group the salaries by the ``fiscal_year`` and ``dept``. - :pipeline:`$merge` to write the result set to the ``budgets`` collection, :ref:`replacing documents ` with the same ``_id`` value (in this example, a document with the fiscal year and dept). For documents that do not have matches in the collection, :pipeline:`$merge` inserts the new documents. .. code-block:: javascript db.getSiblingDB("zoo").salaries.aggregate( [ { $match : { fiscal_year: { $gte : 2019 } } }, { $group: { _id: { fiscal_year: "$fiscal_year", dept: "$dept" }, salaries: { $sum: "$salary" } } }, { $merge : { into: { db: "reporting", coll: "budgets" }, on: "_id", whenMatched: "replace", whenNotMatched: "insert" } } ] ) After the aggregation is run, view the documents in the ``budgets`` collection: .. code-block:: javascript db.getSiblingDB("reporting").budgets.find().sort( { _id: 1 } ) The ``budgets`` collection incorporates the new salary data for fiscal year 2019 and adds new documents for fiscal year 2020: .. code-block:: javascript :emphasize-lines: 5-7 { "_id" : { "fiscal_year" : 2017, "dept" : "A" }, "salaries" : 220000 } { "_id" : { "fiscal_year" : 2017, "dept" : "Z" }, "salaries" : 115000 } { "_id" : { "fiscal_year" : 2018, "dept" : "A" }, "salaries" : 215000 } { "_id" : { "fiscal_year" : 2018, "dept" : "Z" }, "salaries" : 280000 } { "_id" : { "fiscal_year" : 2019, "dept" : "A" }, "salaries" : 275000 } { "_id" : { "fiscal_year" : 2019, "dept" : "Z" }, "salaries" : 410000 } { "_id" : { "fiscal_year" : 2020, "dept" : "Z" }, "salaries" : 240000 } .. seealso:: :doc:`/core/materialized-views` .. _merge-mat-view-insert-only: Only Insert New Data ~~~~~~~~~~~~~~~~~~~~ .. container:: To ensure that the :pipeline:`$merge` does not overwrite existing data in the collection, set :ref:`whenMatched ` to :ref:`keepExisting ` or :ref:`fail `. The example ``salaries`` collection in the ``zoo`` database contains the employee salary and department history: .. code-block:: javascript { "_id" : 1, employee: "Ant", dept: "A", salary: 100000, fiscal_year: 2017 }, { "_id" : 2, employee: "Bee", dept: "A", salary: 120000, fiscal_year: 2017 }, { "_id" : 3, employee: "Cat", dept: "Z", salary: 115000, fiscal_year: 2017 }, { "_id" : 4, employee: "Ant", dept: "A", salary: 115000, fiscal_year: 2018 }, { "_id" : 5, employee: "Bee", dept: "Z", salary: 145000, fiscal_year: 2018 }, { "_id" : 6, employee: "Cat", dept: "Z", salary: 135000, fiscal_year: 2018 }, { "_id" : 7, employee: "Gecko", dept: "A", salary: 100000, fiscal_year: 2018 }, { "_id" : 8, employee: "Ant", dept: "A", salary: 125000, fiscal_year: 2019 }, { "_id" : 9, employee: "Bee", dept: "Z", salary: 160000, fiscal_year: 2019 }, { "_id" : 10, employee: "Cat", dept: "Z", salary: 150000, fiscal_year: 2019 } A collection ``orgArchive`` in the ``reporting`` database contains historical departmental organization records for the past fiscal years. Archived records should not be modified. .. code-block:: javascript { "_id" : ObjectId("5cd8c68261baa09e9f3622be"), "employees" : [ "Ant", "Gecko" ], "dept" : "A", "fiscal_year" : 2018 } { "_id" : ObjectId("5cd8c68261baa09e9f3622bf"), "employees" : [ "Ant", "Bee" ], "dept" : "A", "fiscal_year" : 2017 } { "_id" : ObjectId("5cd8c68261baa09e9f3622c0"), "employees" : [ "Bee", "Cat" ], "dept" : "Z", "fiscal_year" : 2018 } { "_id" : ObjectId("5cd8c68261baa09e9f3622c1"), "employees" : [ "Cat" ], "dept" : "Z", "fiscal_year" : 2017 } The ``orgArchive`` collection has a unique compound index on the ``fiscal_year`` and ``dept`` fields; i.e. there should be at most one record for the same fiscal year and department combination: .. code-block:: javascript db.getSiblingDB("reporting").orgArchive.createIndex ( { fiscal_year: 1, dept: 1 }, { unique: true } ) At the end of current fiscal year (``2019`` in this example), the ``salaries`` collection contain the following documents: .. code-block:: javascript { "_id" : 1, "employee" : "Ant", "dept" : "A", "salary" : 100000, "fiscal_year" : 2017 } { "_id" : 2, "employee" : "Bee", "dept" : "A", "salary" : 120000, "fiscal_year" : 2017 } { "_id" : 3, "employee" : "Cat", "dept" : "Z", "salary" : 115000, "fiscal_year" : 2017 } { "_id" : 4, "employee" : "Ant", "dept" : "A", "salary" : 115000, "fiscal_year" : 2018 } { "_id" : 5, "employee" : "Bee", "dept" : "Z", "salary" : 145000, "fiscal_year" : 2018 } { "_id" : 6, "employee" : "Cat", "dept" : "Z", "salary" : 135000, "fiscal_year" : 2018 } { "_id" : 7, "employee" : "Gecko", "dept" : "A", "salary" : 100000, "fiscal_year" : 2018 } { "_id" : 8, "employee" : "Ant", "dept" : "A", "salary" : 125000, "fiscal_year" : 2019 } { "_id" : 9, "employee" : "Bee", "dept" : "Z", "salary" : 160000, "fiscal_year" : 2019 } { "_id" : 10, "employee" : "Cat", "dept" : "Z", "salary" : 150000, "fiscal_year" : 2019 } { "_id" : 11, "employee" : "Wren", "dept" : "Z", "salary" : 100000, "fiscal_year" : 2019 } { "_id" : 12, "employee" : "Zebra", "dept" : "A", "salary" : 150000, "fiscal_year" : 2019 } { "_id" : 13, "employee" : "headcount1", "dept" : "Z", "salary" : 120000, "fiscal_year" : 2020 } { "_id" : 14, "employee" : "headcount2", "dept" : "Z", "salary" : 120000, "fiscal_year" : 2020 } To update the ``orgArchive`` collection to include the fiscal year ``2019`` that has just ended, the following aggregation pipeline uses: - :pipeline:`$match` stage to find all documents with ``fiscal_year`` equal to ``2019``. - :pipeline:`$group` stage to group the employees by the ``fiscal_year`` and ``dept``. - :pipeline:`$project` stage to suppress the ``_id`` field and add separate ``dept`` and ``fiscal_year`` field. When the documents are passed to :pipeline:`$merge`, :pipeline:`$merge` automatically generates a new ``_id`` field for the documents. - :pipeline:`$merge` to write the result set to ``orgArchive``. The :pipeline:`$merge` stage matches documents :ref:`on ` the ``dept`` and ``fiscal_year`` fields and ``fails`` when matched. That is, if a document already exists for the same department and fiscal year, the :pipeline:`$merge` errors. .. code-block:: javascript db.getSiblingDB("zoo").salaries.aggregate( [ { $match: { fiscal_year: 2019 }}, { $group: { _id: { fiscal_year: "$fiscal_year", dept: "$dept" }, employees: { $push: "$employee" } } }, { $project: { _id: 0, dept: "$_id.dept", fiscal_year: "$_id.fiscal_year", employees: 1 } }, { $merge : { into : { db: "reporting", coll: "orgArchive" }, on: [ "dept", "fiscal_year" ], whenMatched: "fail" } } ] ) After the operation, the ``orgArchive`` collection contains the following documents: .. code-block:: javascript :emphasize-lines: 3,6 { "_id" : ObjectId("5caccc6a66b22dd8a8cc419f"), "employees" : [ "Ahn", "Bess" ], "dept" : "A", "fiscal_year" : 2017 } { "_id" : ObjectId("5caccc6a66b22dd8a8cc419e"), "employees" : [ "Ahn", "Gee" ], "dept" : "A", "fiscal_year" : 2018 } { "_id" : ObjectId("5caccd0b66b22dd8a8cc438e"), "employees" : [ "Ahn", "Zeb" ], "dept" : "A", "fiscal_year" : 2019 } { "_id" : ObjectId("5caccc6a66b22dd8a8cc41a0"), "employees" : [ "Carl" ], "dept" : "Z", "fiscal_year" : 2017 } { "_id" : ObjectId("5caccc6a66b22dd8a8cc41a1"), "employees" : [ "Bess", "Carl" ], "dept" : "Z", "fiscal_year" : 2018 } { "_id" : ObjectId("5caccd0b66b22dd8a8cc438d"), "employees" : [ "Bess", "Carl", "Wen" ], "dept" : "Z", "fiscal_year" : 2019 } If the ``orgArchive`` collection already contained a document for 2019 for department ``"A"`` and/or ``"B"``, the aggregation :ref:`fails because of the duplicate key error `. However, any document inserted before the error will not be rolled back. If you specify :ref:`keepExisting ` for the matching document, the aggregation does not affect the matching document and does not error with duplicate key error. Similarly, if you specify :ref:`replace `, the operation would not fail; however, the operation would replace the existing document. .. _merge-mat-view-merge: Merge Results from Multiple Collections ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: By default, if a document in the aggregation results matches a document in the collection, the :pipeline:`$merge` stage :ref:`merges ` the documents. An example collection ``purchaseorders`` is populated with the purchase order information by quarter and regions: .. code-block:: javascript db.purchaseorders.insertMany( [ { _id: 1, quarter: "2019Q1", region: "A", qty: 200, reportDate: new Date("2019-04-01") }, { _id: 2, quarter: "2019Q1", region: "B", qty: 300, reportDate: new Date("2019-04-01") }, { _id: 3, quarter: "2019Q1", region: "C", qty: 700, reportDate: new Date("2019-04-01") }, { _id: 4, quarter: "2019Q2", region: "B", qty: 300, reportDate: new Date("2019-07-01") }, { _id: 5, quarter: "2019Q2", region: "C", qty: 1000, reportDate: new Date("2019-07-01") }, { _id: 6, quarter: "2019Q2", region: "A", qty: 400, reportDate: new Date("2019-07-01") }, ] ) Another example collection ``reportedsales`` is populated with the reported sales information by quarter and regions: .. code-block:: javascript db.reportedsales.insertMany( [ { _id: 1, quarter: "2019Q1", region: "A", qty: 400, reportDate: new Date("2019-04-02") }, { _id: 2, quarter: "2019Q1", region: "B", qty: 550, reportDate: new Date("2019-04-02") }, { _id: 3, quarter: "2019Q1", region: "C", qty: 1000, reportDate: new Date("2019-04-05") }, { _id: 4, quarter: "2019Q2", region: "B", qty: 500, reportDate: new Date("2019-07-02") }, ] ) Assume that, for reporting purposes, you want to view the data by quarter in the following format: .. code-block:: javascript { "_id" : "2019Q1", "sales" : 1950, "purchased" : 1200 } { "_id" : "2019Q2", "sales" : 500, "purchased" : 1700 } You can use the :pipeline:`$merge` to merge in results from the ``purchaseorders`` collection and the ``reportedsales`` collection to create a new collection ``quarterlyreport``. To create the ``quarterlyreport`` collection, you can use the following pipeline: .. code-block:: javascript db.purchaseorders.aggregate( [ { $group: { _id: "$quarter", purchased: { $sum: "$qty" } } }, // group purchase orders by quarter { $merge : { into: "quarterlyreport", on: "_id", whenMatched: "merge", whenNotMatched: "insert" } } ]) First stage: The :pipeline:`$group` stage groups by the quarter and uses :expression:`$sum` to add the ``qty`` fields into a new ``purchased`` field. For example: .. code-block:: javascript :copyable: false { "_id" : "2019Q2", "purchased" : 1700 } { "_id" : "2019Q1", "purchased" : 1200 } Second stage: The :pipeline:`merge` stage writes the documents to the ``quarterlyreport`` collection in the same database. If the stage finds an existing document in the collection that :ref:`matches on ` the ``_id`` field, the stage merges the matching documents. Otherwise, the stage inserts the document. For the initial creation, no documents should match. To view the documents in the collection, run the following operation: .. code-block:: javascript db.quarterlyreport.find().sort( { _id: 1 } ) The collection contains the following documents: .. code-block:: javascript :copyable: false { "_id" : "2019Q1", "sales" : 1200, "purchased" : 1200 } { "_id" : "2019Q2", "sales" : 1700, "purchased" : 1700 } Similarly, run the following aggregation pipeline against the ``reportedsales`` collection to merge the sales results into the ``quarterlyreport`` collection. .. code-block:: javascript db.reportedsales.aggregate( [ { $group: { _id: "$quarter", sales: { $sum: "$qty" } } }, // group sales by quarter { $merge : { into: "quarterlyreport", on: "_id", whenMatched: "merge", whenNotMatched: "insert" } } ]) First stage: The :pipeline:`$group` stage groups by the quarter and uses :expression:`$sum` to add the ``qty`` fields into a new ``sales`` field. For example: .. code-block:: javascript :copyable: false { "_id" : "2019Q2", "sales" : 500 } { "_id" : "2019Q1", "sales" : 1950 } Second stage: The :pipeline:`merge` stage writes the documents to the ``quarterlyreport`` collection in the same database. If the stage finds an existing document in the collection that :ref:`matches on ` the ``_id`` field (the quarter), the stage merges the matching documents. Otherwise, the stage inserts the document. To view the documents in the ``quarterlyreport`` collection after the data has been merged, run the following operation: .. code-block:: javascript db.quarterlyreport.find().sort( { _id: 1 } ) The collection contains the following documents: .. code-block:: javascript :copyable: false { "_id" : "2019Q1", "sales" : 1950, "purchased" : 1200 } { "_id" : "2019Q2", "sales" : 500, "purchased" : 1700 } Use the Pipeline to Customize the Merge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .. container:: The :pipeline:`$merge` can use a :ref:`custom update pipeline ` when documents match. The :ref:`whenMatched pipeline ` can have the following stages: - :pipeline:`$addFields` and its alias :pipeline:`$set` - :pipeline:`$project` and its alias :pipeline:`$unset` - :pipeline:`$replaceRoot` and its alias :pipeline:`$replaceWith` An example collection ``votes`` is populated with the daily vote tally. Create the collection with the following documents:s .. code-block:: javascript db.votes.insertMany([ { date: new Date("2019-05-01"), "thumbsup" : 1, "thumbsdown" : 1 }, { date: new Date("2019-05-02"), "thumbsup" : 3, "thumbsdown" : 1 }, { date: new Date("2019-05-03"), "thumbsup" : 1, "thumbsdown" : 1 }, { date: new Date("2019-05-04"), "thumbsup" : 2, "thumbsdown" : 2 }, { date: new Date("2019-05-05"), "thumbsup" : 6, "thumbsdown" : 10 }, { date: new Date("2019-05-06"), "thumbsup" : 13, "thumbsdown" : 16 } ]) Another example collection ``monthlytotals`` has the up-to-date monthly vote totals. Create the collection with the following document: .. code-block:: javascript db.monthlytotals.insertOne({ "_id" : "2019-05", "thumbsup" : 26, "thumbsdown" : 31 } ) At the end of each day, that day's votes is inserted into the ``votes`` collection: .. code-block:: javascript db.votes.insertOne( { date: new Date("2019-05-07"), "thumbsup" : 14, "thumbsdown" : 10 } ) You can use :pipeline:`$merge` with an custom pipeline to update the existing document in the collection ``monthlytotals``: .. code-block:: javascript db.votes.aggregate([ { $match: { date: { $gte: new Date("2019-05-07"), $lt: new Date("2019-05-08") } } }, { $project: { _id: { $dateToString: { format: "%Y-%m", date: "$date" } }, thumbsup: 1, thumbsdown: 1 } }, { $merge: { into: "monthlytotals", on: "_id", whenMatched: [ { $addFields: { thumbsup: { $add:[ "$thumbsup", "$$new.thumbsup" ] }, thumbsdown: { $add: [ "$thumbsdown", "$$new.thumbsdown" ] } } } ], whenNotMatched: "insert" } } ]) First stage: The :pipeline:`$match` stage finds the specific day's votes. For example: .. code-block:: javascript :copyable: false { "_id" : ObjectId("5ce6097c436eb7e1203064a6"), "date" : ISODate("2019-05-07T00:00:00Z"), "thumbsup" : 14, "thumbsdown" : 10 } Second stage: The :pipeline:`$project` stage sets the ``_id`` field to a year-month string. For example: .. code-block:: javascript :copyable: false { "thumbsup" : 14, "thumbsdown" : 10, "_id" : "2019-05" } Third stage: The :pipeline:`merge` stage writes the documents to the ``monthlytotals`` collection in the same database. If the stage finds an existing document in the collection that :ref:`matches on ` the ``_id`` field, the stage uses a pipeline to add the ``thumbsup`` votes and the ``thumbsdown`` votes. - This pipeline cannot directly accesses the fields from the results document. To access the ``thumbsup`` field and the ``thumbsdown`` field in the results document, the pipeline uses the ``$$new`` variable; i.e. ``$$new.thumbsup`` and ``$new.thumbsdown``. - This pipeline can directly accesses the ``thumbsup`` field and the ``thumbsdown`` field in the existing document in the collection; i.e. ``$thumbsup`` and ``$thumbsdown``. The resulting document replaces the existing document. To view documents in the ``monthlytotals`` collection after the merge operation, run the following operation: .. code-block:: javascript db.monthlytotals.find() The collection contains the following document: .. code-block:: javascript :copyable: false { "_id" : "2019-05", "thumbsup" : 40, "thumbsdown" : 41 }