CODEWITHSUNDEEP
sqlarraysjsonazureazure-cosmosdb
Lynn
Lynn

Reputation: 4408

Query for entire JSON document in nested JSON schema

Background:

I wish to locate the entire JSON document that has a condition where "state" = "new" and where length(Features.id) > 4

{
    "id": "123"
    "feedback": {
        "Features": [
            {
                "state": "new"
                "id": "12345"

            }

        ]
    }
}

This is what I have tried to do:

Since this is a nested document. My query looks like this:

A stackoverflow member has helped me to access the nested contents within the query, but is there a way to obtain the full document

I have used:

  SELECT VALUE t.id FROM t IN f.feedback.Features where t.state = 'new' and length(t.id)>4

This will give me the ids.

My desire is to have access to the full document with this condition?

{
    "id": "123"
    "feedback": {
        "Features": [
            {
                "state": "new"
                "id": "12345"

            }

        ]
    }
}

Any help is appreciated

Upvotes: 0

Views: 232

Answers (1)

K4M
K4M

Reputation: 952

Try this

SELECT * 
FROM f 
WHERE 
    f.feedback.Features[0].state = 'new' 
    AND length(f.feedback.Features[0].id)>4

Here is the SELECT spec for CosmosDB for more details

https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-select

Also, check out "working with JSON" in CosmosDB notes

https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-working-with-json

If the Features array has more than 1 value, you can use EXISTS clause to search within them. See specs of EXISTS here with examples:

https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-subquery#exists-expression

Upvotes: 1

Related Questions