CODEWITHSUNDEEP
sqlazure-cosmosdb
ANKIT
ANKIT

Reputation: 461

SELECT Array elements using Cosmos SQL query

Need to select array elements as row element.

Cosmos Documents JSON

1.

{
  "CountyId": 1
  "CountyCode": "Abbeville",
  "Cities": [
    { "CityId": 1, "CityName": "Arborville" }
  ]
}

2.

{
  "CountyId": 2
  "CountyCode": "Adair",
  "Cities": [
    { "CityId": 2, "CityName": "Ballard" },
    { "CityId": 3, "CityName": "Brashear" },
  ]
}

And the result that I need would like this.

enter image description here

Upvotes: 0

Views: 186

Answers (1)

Gaurav Mantri
Gaurav Mantri

Reputation: 136146

Please try the following query:

SELECT c.CountyId, c.CountyCode, d.CityId, d.CityName FROM c
Join d in c.Cities

This produces the following output:

[
    {
        "CountyId": 1,
        "CountyCode": "Abbeville",
        "CityId": 1,
        "CityName": "Arborville"
    },
    {
        "CountyId": 2,
        "CountyCode": "Adair",
        "CityId": 2,
        "CityName": "Ballard"
    },
    {
        "CountyId": 2,
        "CountyCode": "Adair",
        "CityId": 3,
        "CityName": "Brashear"
    }
]

enter image description here

Upvotes: 1

Related Questions