Query for entire JSON document in nested JSON schema

Question

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

like this? SELECT VALUE f.id FROM t IN f.feedback.Features where t.state = 'new' and length(t.id)>4 — Lynn
– Lynn, Commented Aug 19, 2020 at 4:46
Just this should do if you want the entire data again. SELECT VALUE f FROM t IN f.feedback.Features where t.state = 'new' ... — codetiger
– codetiger, Commented Aug 19, 2020 at 4:50

K4M · Accepted Answer · 2020-08-19 05:04:50Z

1

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

edited Aug 19, 2020 at 5:04

answered Aug 19, 2020 at 4:51

K4M

9624 silver badges16 bronze badges

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

1 Comment

Lynn Over a year ago

ok thank you. This did not work for me. Perhaps its not working because the 'Features' is an array?

Collectives™ on Stack Overflow

Query for entire JSON document in nested JSON schema

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related