Cosmos DB SQL nested array query-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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.