# merge

The `$merge` operator writes the result of an aggregation pipeline to a specified collection. This operator must be the last stage in a pipeline.

## Syntax

```mongodb
{ $merge: {
     into: <collection> -or- { db: <db>, coll: <collection> },
     on: <identifier field>,  // Optional,
     whenMatched: <replace|keepExisting|merge|fail>,  // Optional 
     whenNotMatched: <insert|discard|fail>            // Optional 
} }
```

| Option                     | Type   | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| -------------------------- | ------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `into`                     | String | Specifies the collection to write the output to. You can either specify a collection in the same database as:`into: "collnName"`or specify a collection in a specified database as:`into: { db: "databaseName", coll: "collnName" }`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| `on`(Optional)             | String | Specifies the field that acts as the unique identifier for the document.SingleStore Kaionly supports the`_id`field in the`on`option.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| `whenMatched`(Optional)    | String | Specifies the behavior of the`$merge`operator if an existing document and the result document in a collection have the same value for the`_id`field. The default value for this option is`merge`. The`pipeline`option is not supported.This option can have the following values:<ul> <li><code>replace</code>: Replaces the existing document in the target collection.</li> <li><code>keepExisting</code>: Retains the existing document in the target collection instead of replacing it.</li> <li><code>merge</code>: Merges the matching documents as follows: <ul> <li>If the result document contains fields that are absent from the existing document, the missing fields are added to the existing document.</li> <li>If the result document contains fields that are present in the existing document, replace the existing field values in the existing document with the values in the result document.</li> </ul></li> <li><code>fail</code>: Stops the aggregation pipeline. Note that any changes to the output collection are not reverted.</li> </ul> |
| `whenNotMatched`(Optional) | String | Specifies the behavior of the`$merge`operator if the result document does not match an existing document in the output collection. The default value for this option is`insert`.This option can have the following three values:<ul> <li><code>insert</code>: Inserts the document in the output collection.</li> <li><code>discard</code>: Discards the document.</li> <li><code>fail</code>: Stops and fails the aggregation pipeline. Note that any changes to the output collection are not reverted.</li> </ul>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |

## Remarks

* The `$merge` operator does not support the `let` option.
* When the `fail` condition is satisfied, the entire query is aborted.

## Example

The following example shows how to use the `$merge` operator. Run the following commands to insert documents in the **testCol1** and **testCol2** collections:

```mongodb
db.getSiblingDB("dbTest").testCol1.insertMany([
   { "_id" : 1, Stock_Code: "S01", Area_Code: "A1", Units: 135000, Year: 2018 },
   { "_id" : 2, Stock_Code: "S02", Area_Code: "A1", Units: 100000, Year: 2018 },
   { "_id" : 3, Stock_Code: "S03", Area_Code: "A1", Units: 125000, Year: 2019 },
   { "_id" : 9, Stock_Code: "S04", Area_Code: "A2", Units: 160000, Year: 2019 },
   { "_id" : 10, Stock_Code: "S01", Area_Code: "A2", Units: 150000, Year: 2019 }
])


db.getSiblingDB("dbTest").testCol2.insertMany([
  { "_id": 1, Stock_Code: "S03", Area_Code: "A1", Units: 100000, Year: 2017, Comment: "TBA" },
  { "_id": 2, Stock_Code: "S04", Area_Code: "A1", Units: 120000, Year: 2017 },
  { "_id": 3, Stock_Code: "S01", Area_Code: "A2", Units: 115000, Year: 2017 },
  { "_id": 4, Stock_Code: "S03", Area_Code: "A1", Units: 115000, Year: 2018 },
  { "_id": 5, Stock_Code: "S04", Area_Code: "A2", Units: 145000, Year: 2018 },
])
```

Now, run the following commands to merge the documents in the **testCol1** collection into the **testCol2** collection and then list the documents in **testCol2**:

```mongodb
db.getSiblingDB("dbTest").testCol1.aggregate({ $merge: { into: { db: "dbTest", coll: "testCol2" }, whenMatched: "merge", whenNotMatched: "insert" } })

db.testCol2.find()

```

```output

[
  {
    _id: 1,
    Area_Code: 'A2',
    Comment: 'TBA',
    Stock_Code: 'S01',
    Units: 135000,
    Year: 2018
  },
  {
    _id: 2,
    Area_Code: 'A1',
    Stock_Code: 'S02',
    Units: 100000,
    Year: 2018
  },
  {
    _id: 3,
    Area_Code: 'A1',
    Stock_Code: 'S03',
    Units: 125000,
    Year: 2019
  },
  {
    _id: 4,
    Area_Code: 'A1',
    Stock_Code: 'S03',
    Units: 115000,
    Year: 2018
  },
  {
    _id: 5,
    Area_Code: 'A2',
    Stock_Code: 'S04',
    Units: 145000,
    Year: 2018
  },
  {
    _id: 9,
    Area_Code: 'A2',
    Stock_Code: 'S04',
    Units: 160000,
    Year: 2019
  },
  {
    _id: 10,
    Area_Code: 'A2',
    Stock_Code: 'S01',
    Units: 150000,
    Year: 2019
  }
]
```

As specified in the command using the `whenMatched: "merge"` and `whenNotMatched: "insert"` options, the matching documents are merged and the non matching documents are inserted in the **testCol2** collection, respectively.

***

Modified at: July 28, 2023

Source: [/cloud/reference/singlestore-kai/singlestore-extension-commands/merge/](https://docs.singlestore.com/cloud/reference/singlestore-kai/singlestore-extension-commands/merge/)

(An index of the documentation is available at /llms.txt)