Query items from CosmosDB based on presense or absense of complex object property

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.