I have mongodb version 5 and latest version of pymongo from pip

I have query which update the $configuration.sql field perfectly fine

Issue: While updating the below $configuration.sql all other fields are lost in the resultset due to $merge statement. can someone correct me if I am missing something I am probably new to mongo queries?

INPUT DOCUMENT STRUCTURE `

{
  "_id": {
    "$oid": "639a60"
  },
  "status": "inactive",
  "version": "0.1",
  "configuration": {
    "identifier": "backfill-test",
    "secondaryIdentifier": "platform_type",
    "sql": "select * from test where lastupd_ts >= '2021-09-10 18:00:00' and lastupd_ts <= '2021-09-10 19:00:00'",
    "steps": [
      {
        "service": "Publish",
        "order": 1,
        "configuration": {
          "topic": "platform-type",
          "type": "PlatformType",
          "action": "N",
          "keyDeserializer": "serializers.Kafka",
          "valueDeserializer": "serializers.Deserializer"
        }
      }
    ]
  },
  "name": "data-exporter-svc"
}

` QUERY USED:

`

database.collection_name.aggregate(
[
        {"$match": {"configuration.identifier": "backfill-test"}},
        {
            "$project":
            {
                "configuration.sql": {"$replaceAll": {"input": "$configuration.sql", "find": "lastupd_ts >= \'2021-09-10 18:00:00\' and lastupd_ts <= \'2021-09-10 19:00:00\'", "replacement": "lastupd_ts >= \'2024-00-10 18:00:00\' and lastupd_ts <= \'2024-00-10 19:00:00\'"}}
            }
        },
        {
            "$merge": "collection_name"
        },
    ])

QUERY RESULT

{
  "_id": {
    "$oid": "639a60"
  },
  "status": "inactive",
  "version": "0.1",
  "configuration": {
    "sql": "select * from test where lastupd_ts >= '2024-00-10 18:00:00' and lastupd_ts <= '2024-00-10 19:00:00'"
  },
  "name": "data-exporter-svc"
}

`

EXPECTED RESULT `

{
  "_id": {
    "$oid": "639a60"
  },
  "status": "inactive",
  "version": "0.1",
  "configuration": {
    "identifier": "backfill-test",
    "secondaryIdentifier": "platform_type",
    "sql": "select * from test where lastupd_ts >= '2024-00-10 18:00:00' and lastupd_ts <= '2024-00-10 19:00:00'",
    "steps": [
      {
        "service": "Publish",
        "order": 1,
        "configuration": {
          "topic": "platform-type",
          "type": "PlatformType",
          "action": "N",
          "keyDeserializer": "serializers.Kafka",
          "valueDeserializer": "serializers.Deserializer"
        }
      }
    ]
  },
  "name": "data-exporter-svc"
}

`

CodePudding user response：

While we could modify this to get it to work with $merge, I think the more straightforward approach would be to adapt this as an update directly. Since the update operation can take an aggregation pipeline to define the modification, this task should be relatively straightforward.

The translation requires doing the following:

1. Take the $match and use its contents as the first parameter to the update (the query filter with which to apply the change to).
2. Use the contents of the $project as the value for $addFields (or its alias $set) as the transformation being applied.
3. The $merge component is replaced by the command being an update rather than an aggregate.

It would look something like this:

db.collection.update({
  "configuration.identifier": "backfill-test"
},
[
  {
    "$set": {
      "configuration.sql": {
        "$replaceAll": {
          "input": "$configuration.sql",
          "find": "lastupd_ts >= '2021-09-10 18:00:00' and lastupd_ts <= '2021-09-10 19:00:00'",
          "replacement": "lastupd_ts >= '2024-00-10 18:00:00' and lastupd_ts <= '2024-00-10 19:00:00'"
        }
      }
    }
  }
])

Playground example here.

Note that I've removed the escape (backslash) characters which you may need to re-introduce depending on the language/quoting that you are using.

Also don't forget that [{$set:{...}}] and {$set:{...}} are different in this context, more info here