In my previous article I showed how to query items from CosmosDB using conditions against nested properties: see Query nested data in CosmosDB. In this post I will show how to query items based on presence or absence of complex object property. Let's use the same example which was used in mentioned post:
[{
"id": "1",
"title": "Foo",
"metadata": {
"metadataId": "123",
"fieldValues": [
{
"fieldName": "field1",
"values": [
"val1"
]
},
{
"fieldName": "field2",
"values": [
"val2"
]
},
{
"fieldName": "field3",
"values": [
"val2"
]
}
]
}
},
{
"id": "2",
"title": "Bar",
},
...
]
So we have 2 objects Foo and Bar. Foo object has metadata property while Bar doesn't. How to fetch all objects which have metadata property? And vice versa: how to fetch those objects which don't have it?
The first attempt would be to compare it with null:
SELECT * FROM c WHERE c.metadata != null
And it works. However if we will try to use the same technique for fetching all items which don't have metadata the following query won't return any results:
SELECT * FROM c WHERE c.metadata = null
Better option which works in both cases will be to use helper function IS_DEFINED. With this function in order to fetch all items which have metadata property we may use the following query:
SELECT * FROM c WHERE IS_DEFINED(c.metadata)
And similarly we may fetch objects which don't have metadata:
SELECT * FROM c WHERE NOT IS_DEFINED(c.dynamicMetadata)
This technique is more universal since it works in both scenarios.
