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.

score 2 · Accepted Answer · answered Aug 23 '22 at 12:20

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

ShaktiSingh-MSFT · Answer 2 · 2022-08-23T05:51:11.963

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 '22 at 05:51

answered Aug 22 '22 at 07:04

ShaktiSingh-MSFT

314
1
4

1

Please insert code code instead of image (as it was done in the question). – C.Champagne Aug 22 '22 at 08:22
I am using f.unit. It still does not give the desired result. – binaryCoder Aug 22 '22 at 13:48
FYI regarding formatted text instead of images of text - see [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) for many reasons why this is important – David Makogon Aug 22 '22 at 16:28

Cosmos DB SQL nested array query

2 Answers2