In Athena how do I query a member of a struct in an array in a struct?
Athena is based on Presto. In Presto 318 you can use any_match:
SELECT * FROM "foo"."test"WHERE any_match(foo.usages, element -> element.usage IS NULL);I think the function is not available in Athena yet, but you can emulate it using reduce.
SELECT * FROM "foo"."test"WHERE reduce( foo.usages, -- array to reducing false, -- initial state (state, element) -> state OR element.usage IS NULL, -- combining function state -> state); -- output function (identity in this case)
You can achieve this by unnesting the array into rows and then check those for null values. This will result in one row per null-value entry.
select * from testCROSS JOIN UNNEST(foo.usages) AS t(i)where i.usage is nullSo if you only nee the unique set, you must run this through a select distinct.
select distinct id from testCROSS JOIN UNNEST(foo.usages) AS t(i)where i.usage is null