SELECT Array elements using Cosmos SQL query

Question

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.

Please edit your question to show what you've tried, where you're stuck, expected vs actual output, etc. As for a place to start: have you looked at doing a self-join (described in the Cosmos DB documentation)? — David Makogon
– David Makogon, Commented Apr 25, 2020 at 13:00
Thanks for your suggestion, I am new to the Cosmos DB so was not aware of it. — ANKIT
– ANKIT, Commented Apr 26, 2020 at 5:50

Gaurav Mantri · Accepted Answer · 2020-04-25 13:17:41Z

1

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

answered Apr 25, 2020 at 13:17

Gaurav Mantri

137k17 gold badges247 silver badges282 bronze badges

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

1 Comment

ANKIT Over a year ago

that is what I was looking for, Thanks!

Collectives™ on Stack Overflow

SELECT Array elements using Cosmos SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related