Sign inTry Free

merge

On this page

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

{ $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 Kai only 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:

  • replace: Replaces the existing document in the target collection.

  • keepExisting: Retains the existing document in the target collection instead of replacing it.

  • merge: Merges the matching documents as follows:

    • If the result document contains fields that are absent from the existing document, the missing fields are added to the existing document.

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

  • fail: Stops the aggregation pipeline. Note that any changes to the output collection are not reverted.

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:

  • insert: Inserts the document in the output collection.

  • discard: Discards the document.

  • fail: Stops and fails the aggregation pipeline. Note that any changes to the output collection are not reverted.

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:

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:

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


db.testCol2.find()
[
  {
    _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.

Last modified: July 28, 2023

Was this article helpful?

On this page

Was this article helpful?