I am storing data in a Cosmos db which looks similar to the example shown below. I need to write a query that will look in the “payload” section to see if a value exists. It is not possible to use the key (eg ‘lastModifiedOn.previousValue’) as these are dynamically created.
<code>{
"externalId": null,
"serviceName": "Test.Api",
"payload": {
"enabled.previousValue": "False",
"enabled.currentValue": "True",
"lastModifiedOn.previousValue": "2024-04-15 11:24:28",
"lastModifiedOn.currentValue": "2024-04-15 11:24:28"
},
"actionType": "Modified"
}
</code>
<code>{
"externalId": null,
"serviceName": "Test.Api",
"payload": {
"enabled.previousValue": "False",
"enabled.currentValue": "True",
"lastModifiedOn.previousValue": "2024-04-15 11:24:28",
"lastModifiedOn.currentValue": "2024-04-15 11:24:28"
},
"actionType": "Modified"
}
</code>
{
"externalId": null,
"serviceName": "Test.Api",
"payload": {
"enabled.previousValue": "False",
"enabled.currentValue": "True",
"lastModifiedOn.previousValue": "2024-04-15 11:24:28",
"lastModifiedOn.currentValue": "2024-04-15 11:24:28"
},
"actionType": "Modified"
}
I have used the following query but it fails to find any records. I had hoped it would find records where the supplied datetime exists as a value in the payload section.
<code>SELECT VALUE root FROM root WHERE (ARRAY_CONTAINS(root["payload"]["values"], "2024-04-15 11:24:28") )
</code>
<code>SELECT VALUE root FROM root WHERE (ARRAY_CONTAINS(root["payload"]["values"], "2024-04-15 11:24:28") )
</code>
SELECT VALUE root FROM root WHERE (ARRAY_CONTAINS(root["payload"]["values"], "2024-04-15 11:24:28") )
Is what I’m trying to do possible?