Search code examples
sqlamazon-web-servicesddl

Query from array structure

I have a db table called ex_table and Location is a column.

when i ran query it shows array structure. I need extract array element.

My Query was

Select location form ex_table it shows

[{country=BD, state=NIL, city=NIL}]

how do I select only city form location column?

Solution

  • Try the following:

    WITH dataset AS (
  SELECT location
  FROM ex_table
)
SELECT places.city
FROM dataset, UNNEST (location) AS t(places)

    As this is an array of objects, you need to flatten the data. This is done using the UNNEST syntax in Athena. More info on this can be found in the AWS documentation