CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
shashank shekhar
shashank shekhar

Reputation: 162

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

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.

Upvotes: 5

Views: 10846

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

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

Upvotes: 10

Related Questions