CODEWITHSUNDEEP
jsonazurenosqlazure-cosmosdbazure-cosmosdb-sqlapi
Lynn
Lynn

Reputation: 4388

Clean way to query complex CosmosDB document

Background:

I wish to locate all the nested Features fields within my JSON document that have 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:

 SELECT
 c.feedback
 ARRAY(SELECT Features.state FROM c IN c.Features) AS Features FROM 
 c
 WHERE length(Features.id) >15

Or perhaps I must do:

   SELECT VALUE c.feedback.Features
   FROM c
   WHERE EXISTS(
   SELECT VALUE n
   FROM c IN c.Features
    WHERE f.state = "NotLinked” AND length(c.id) > 15
 )

However, the syntax is not correct and I am currently researching and looking for examples for this case

Any help is appreciated

Upvotes: 1

Views: 174

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

Please try this sql:

SELECT DISTINCT c.feedback FROM c join d in c.feedback.Features where d.state = "new" and length(d.id) > 4

You can refer to this documentation.

Upvotes: 2

Related Questions