Sign inTry Free

Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

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:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In