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

Question

[1st err while querying][1]

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

Use `json_extract`. See: [6.12. JSON Functions and Operators — Presto Documentation](https://prestodb.github.io/docs/current/functions/json.html) — John Rotenstein, Mar 15 '19 at 06:55
Please edit your question to show what you tried and what happened. — John Rotenstein, Mar 15 '19 at 07:04

score 6 · Accepted Answer · answered Mar 15 '19 at 08:25

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.

1 Answers1