$merge whenMatched pipeline

user_13033029 0
Viewed 467

I have been looking at $merge and Variables in Aggregation Expressions but I am struggling to understand. What I would like to do in a very general sense is take two collections, match them on the unique "Role ID" field and see if they are exactly the same or not. If they are the same I want to update the "Status" field to "Updated".

Where I am struggling is on the whenMatched pipeline. I am not sure how to target the "new" and "old" document for the $cmp expression. I am also not tied to this approach. I feel like $mergeObjects could be used as well. I appreciate the help.

const mergePipeline = [
    {'$unset': "_id"},
    {'$addFields' : {"Status" : "New"}},
    {'$merge' : {
        into: "previous",
        on: "Role ID",
        whenMatched: [
            // compare the documents with $cmp <-- is it possible to only compare a few fields without unsetting them?
            // if different replace root with "new" document
            // change status to "updated"
        ],
        whenNotMatched: "insert"
    }}
];

db.current.aggregate(mergePipeline);
mongodb
2 Answers

Coll1 (we aggregate on this one)

[
  {
    "role_id": 1,
    "a": 2
  },
  {
    "role_id": 2,
    "a": 3
  },
  {
    "role_id": 3,
    "a": 20
  }
]

Coll2 (the one in the disk)

  • first should be updated (= roots)
  • second should be replaced from the pipeline
  • and rold_id :3 has no match => inserted
[
  {
    "role_id": 1,      
    "a": 2
  },
  {
    "role_id": 2,
    "a": 10
  }
]

Query

  • removes the :_id (else error, we cant update that)
  • merge on role_id
    • if 2 roots(from pipeline and from disk) equals => status updated
    • else replace with the root of the pipeline

*let is used to have the ROOT. of the pipeline, as variable "$$p-root"

coll1.aggregate(
[
  {
    "$unset": [
      "_id"
    ]
  },
  {
    "$merge": {
      "into": {
        "db": "testdb",
        "coll": "coll2"
      },
      "on": [
        "role_id"
      ],
      "let": {
        "p_root": "$$ROOT"
      },
      "whenMatched": [
        {
          "$unset": [
            "_id"
          ]
        },
        {
          "$replaceRoot": {
            "newRoot": {
              "$cond": [
                {
                  "$eq": [
                    "$$p_root",
                    "$$ROOT"
                  ]
                },
                {
                  "$mergeObjects": [
                    "$$ROOT",
                    {
                      "status": "updated"
                    }
                  ]
                },
                "$$p_root"
              ]
            }
          }
        }
      ],
      "whenNotMatched": "insert"
    }
  }
])

Results that i got (i used simple $eq , you can use $cmp but i dont think we need it, because we care only for the equality not the > <)

[
  {
    "role_id": 1,
    "a": 2,
    "status": "updated"     // roots were equal (pipeline root,disk root)
  },
  {
    "role_id": 2,          // root not equal i kept the pipelines
    "a": 3
  },
  {
    "role_id": 3,         // no match happened => insert
    "a": 20
  }
]
user_48826920 
const mergePipeline = [
    {'$unset': "_id"},
    {'$merge' : {
        into: "currentStatusSample",
        on: "Role ID",
        whenMatched: [
            {$unset: ["_id", "Status"]},
            {$addFields : {compare: {$cmp: ["$$new","$$ROOT"]}}},
            {$set: {"Status" :  {$cond: [ {$ne : ["$compare", 0]}, "Updated", "Unchanged"]}}},
            {$unset: "compare"}
        ],
        whenNotMatched: "insert"
    }}
];

I wish there was a way to only $cmp on specified fields but for now this seemed to have worked. I plan on moving forward with either this or the answer posted by Takis above.

user_130330290
Related