Update, aggregation pipeline in pymongo with using $merge operation to update the collection which is deleting all existing fields in the nested field

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"
}

When you write

you indicate that you are only interested in the sql field of the configuration object. To make it work I think you would need to $mergeObjects using the old configuration and the updated sql field.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.