Cosmos db query to fetch data where array item is not null

Question

I have Collection items in my cosmos db container.My sample data is

{
    "objectID": "abc",
    "id": "123",
    "name": "gfh",
    "description": "chock",
    "brand": "hcn",
    "size": 180,
    "sizeUnits": "Grams",
    "stores": []
},
{
    "objectID": "123c",
    "id": "0dfg",
    "name": "shaek",
    "description": "7ihk",
    "brand": "fghcn",
    "size": 768,
    "sizeUnits": "Grams",
    "stores": [ {
            "id": 678",
            "price": 2.2
        },
{
 "id": 678",
            "price": 2.2}]
}

and so on...

I need to fetch all the details whose stores is empty. How to write query for this.

Assess

Answer 1

Please try this SQL:

SELECT * FROM c where ARRAY_LENGTH(c.stores) > 0

Result:

[
    {
        "objectID": "123c",
        "id": "0dfg",
        "name": "shaek",
        "description": "7ihk",
        "brand": "fghcn",
        "size": 768,
        "sizeUnits": "Grams",
        "stores": [
            {
                "id": 678,
                "price": 2.2
            },
            {
                "id": 678,
                "price": 2.2
            }
        ]
    }
]

Laevorotation

I think OP is asking for empty stores, so probably the query needs to be tweaked to: SELECT * FROM c where ARRAY_LENGTH(c.stores) = 0 . If c.stores is an optional property, then it needs to be SELECT * FROM c where ARRAY_LENGTH(c.stores) = 0 or NOT IS_DEFINED(c.stores) – Lepido

@KrishnanSundaram OP's question has been edited. He wanted to fetch all the details whose stores is not null. However, his sample data shows empty array. So I think he wants to get the details whose stores is not empty. This is why I gave the above SQL. – Laevorotation