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