I have the following query in Athena, I am using S3 as data store. I have 2 query parameters in the lambda expression.
PREPARE my_select1 FROM
SELECT address.phonenumbers FROM \"nested-query-db\".\"data_with_json1\" where
cardinality(filter(address.phonenumbers,js->js.type = ? and js.number = ?)) >0 and
cardinality(filter(address.phonenumbers,js->js.type = 'city'and js.number = '4')) > 0 and
firstname ='Emily';
When I execute it using
EXECUTE my_select1 USING 'Home', '1';
It throws the following error.
java.lang.RuntimeException: Query Failed to run with Error Message: SYNTAX_ERROR: line 1:1:
Sample Data:
{"firstname":"Emily","address":{"streetaddress":"101","city":"abc","state":"","phonenumbers":[{"type":"home","number":"11"},{"type":"city","number":"4"}]}}
@jccampanero explain lambda expression does not support in Athena 2.
But I have a workaround for you.
This query works well, according your needs.