CODEWITHSUNDEEP
sqlamazon-athenaprestotrino
Viviane Alves Lima
Viviane Alves Lima

Reputation: 19

Get the first value of a json

I have a json like this in an Athena table:

 input 
 [14587979, {ke1:4,ke2:4}]

I would like to get the fist value "14587979"

but when I use json_extract like this:

json_extract(input, "$[0]") as id

I get the below error

Query execution failed

Reason: SQL Error [100071] [HY000]: [Simba]AthenaJDBC An error has been thrown from the AWS Athena client. SYNTAX_ERROR: line 3:22: Column '$[0]' cannot be resolved

how Can I get the first value?

Upvotes: 0

Views: 70

Answers (1)

Guru Stron
Guru Stron

Reputation: 142448

Use single quotes, double quotes are used to escape column/table/schema names (if for some example the name contains restricted character like space):

select json_extract('[14587979, {ke1:4,ke2:4}]', '$[0]');

Output:

_col0
14587979

Upvotes: 1

Related Questions