CODEWITHSUNDEEP
sqlamazon-web-servicesddl
uannabi
uannabi

Reputation: 184

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?

Upvotes: 0

Views: 1007

Answers (1)

Barry Piccinni
Barry Piccinni

Reputation: 1801

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

Upvotes: 1

Related Questions