CODEWITHSUNDEEP
javascriptjavamysqlamazon-web-servicesamazon-athena
Amit kumar Sahu
Amit kumar Sahu

Reputation: 23

how to query(search) of sql on Amazon Athena which has JSON value?

[1st err while querying][1]

Screenshot of the data of Athena:

select * from table where properties 'year' = 2007 // is not working
//please check the screen shot of the table

I want to query on Athena dataset throug sql query. I tried every query but its not working on this Athena data

Upvotes: 1

Views: 3546

Answers (1)

Theo
Theo

Reputation: 132862

I assume the properties column is a STRING, in that case you can do this to extract the year field and use it in a filter:

SELECT * FROM table WHERE JSON_EXTRACT_SCALAR(properties, '$.year') = '2007'

Notice that it's '2007', not 2007, since from your screenshot it looks like the values are strings.

You can read more about the JSON_EXTRACT_SCALAR function and other JSON manipulation functions in the documentation for the Presto version that Athena is currently using.

Upvotes: 6

Related Questions