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.

Steve Johnson · Accepted Answer · 2021-06-24 12:11:04Z

12

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

answered Jun 24, 2021 at 12:11

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Krishnan Sundaram Over a year ago

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)

Steve Johnson Over a year ago

@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.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related