Search code examples
sqlamazon-athenaprestotrino

sql athena/presto check if value in array of nested json

I run a query in athena like so:

SELECT element_at(col_name,1).entities FROM "db_name"."the_table" limit 10;

and I get an array of nested jsons/dicts like so:

[{country=US,content=content},{country=DE,content=content},{country=GB,content=content}]
[]
[{country=HK,content=content},{country=IN,content=content},{country=GB,content=content}]
[]

and I want to create a query which extracts the row if any of the country=US.

So I tried:

SELECT * FROM "db_name"."the_table" WHERE any_match(element_at(col_name,1).entities, e-> e.country= 'US') limit 10;

but this crashes saying its not a row

I would like to extract rows where the country is US..

Solution

  • any_match(element_at(col_name,1).entities, e -> e.country = 'US') should work for ROW type.

    There is another type which can be written in output in this way - it is MAP, so you can try:

    WHERE any_match(element_at(col_name,1).entities, e -> e['country'] = 'US')

    or (if the key is optional):

    WHERE any_match(element_at(col_name,1).entities, e-> element_at(e, 'country') = 'US')