Trying to filter a json array for a specific object
select json_extract(
'{"coll":[{"key":"color","value":"red"},{"key":"other","value":"whatever"}]}',
'$.coll[?(@.type == ''color'')]'
)
but AWS athena just says
INVALID_FUNCTION_ARGUMENT: Invalid JSON path: '$.coll[?(@.type == 'color')]'
Trying the above in a tool like https://jsonpath.com/ seems to work.
Is this syntax correct and athena just does not support this kind of json path?