Cosmos DB SQL nested array query

Question

I am trying to write a query for Cosmos DB which would involve filtering nested arrays. I have a schema similar to

{
"studentname": {
   "first": "John",
   "last": "Doe",
},
"information":{
    "address":[
        {
           "county" : "Derby",
           "street": [
                 {
                   "unit" : "123",
                   "name" : "abc"
                 },
                 {
                   "unit" : "098",
                   "name" : "efs"
                 }
              ]
           },
           {
           "county" : "Essex",
           "street": [
                 {
                   "unit" : "456",
                   "name" : "zzz"
                 },
                 {
                   "unit" : "765",
                   "name" : "aaa"
                 }
              ]
           }
        ]
      },
        "extra": "n/a"
}

I am trying to get:

{
"studentname":{
    "first": "John"
   },
   "information":{
       "address":[
           {
            "street":[
                  {
                   "unit": "123"
                  },
                  {
                   "unit": "098"
                  }
                ]
             },
             {
               "street":[
                   {
                     "unit": "456"
                   },
                   {
                     "unit": "765"
                   }
              ]
          }
       ]
   }
}

I have tried this query but it does not give desired result

SELECT 
{'first': root.studentname.first} as studentname,
{'address': [{'street': ARRAY(SELECT f.unit FROM f IN c.street)}]} as information
FROM root
JOIN c in root.information.address

The output for the query I used is:

{
"studentname": {
   "first": "John"
},
"information":{
    "address":[
        {
           "street": [
                 {
                   "unit" : "123"
                 },
                 {
                   "unit" : "098"
                 }
              ]
           }
        ]
      }
  },
   {
    "studentname": {
    "first": "John"
   },
     "information":{
        "address":[
          {
           "street": [
                 {
                   "unit" : "456"
                 },
                 {
                   "unit" : "765"
                 }
              ]
           }
        ]
      }
  }

I get the studentname.first and information.address array repeated in the result, which is not required I just want a single information.address array which has the street and unit in it as displayed above.

Singh-MSFT · Accepted Answer · 2022-08-23 12:20:59Z

2

In order to get a single information.address array which has the street and unit , use below query:

SELECT {"first": c.studentname.first} as studentname,

    {"address": ARRAY(SELECT ARRAY(SELECT x.unit FROM x in f.street) as street FROM f IN c.information.address)} as information FROM c

answered Aug 23, 2022 at 12:20

Singh-MSFT

3721 silver badge4 bronze badges

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

Comments

Singh-MSFT · Accepted Answer · 2022-08-23 05:51:11Z

0

Please use "f.unit" as shown in below:

select distinct o.studentname,o.information from(SELECT   {'first': root.studentname.first} as studentname, {'address': [{'street':ARRAY(SELECT f.unit FROM f IN c.street)}]} as information FROM root JOIN c in root.information.address  ) as o

Please share error/expected result if required output is not achieved using above.

edited Aug 23, 2022 at 5:51

answered Aug 22, 2022 at 7:04

Singh-MSFT

3721 silver badge4 bronze badges

3 Comments

C.Champagne Over a year ago

Please insert code code instead of image (as it was done in the question).

binaryCoder Over a year ago

I am using f.unit. It still does not give the desired result.

David Makogon Over a year ago

FYI regarding formatted text instead of images of text - see Please do not upload images of code/data/errors when asking a question. for many reasons why this is important

Collectives™ on Stack Overflow

Cosmos DB SQL nested array query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related