Athena query failing while using UNNEST(x) AS y
Hi, I am trying to run the following query using UNNEST
SELECT keywords FROM "query-annotation"."query_annotation" CROSS JOIN UNNEST(browse_nodes) AS single_browse_node WHERE marketplace_id=3 AND single_browse_node='x' LIMIT 50;
The above query is failing with the following error -
SYNTAX_ERROR: line 3:28: Column 'single_browse_node' cannot be resolved
Although I am creating the unnest column with the given name but still it is failing. How do I fix this ? Any help would be really appreciated.
EDIT: If I try the following
SELECT * FROM "query-annotation"."query_annotation" CROSS JOIN UNNEST(browse_nodes) AS single_browse_node WHERE marketplace_id=3 LIMIT 50;, this prints a column at the last with the heading _col21 having single_browse_node value in it and is not picking up the assigned name.
Try changing "UNNEST(browse_nodes) AS single_browse_node" to "UNNEST(browse_nodes) AS t(single_browse_node)".
It tells Athena to, for each row, flatten the array browse_nodes into a relation called t that has a column called single_browse_node. The alias t is arbitrary.
Simple Join query errors out with "table not found"asked a month ago
Athena Iceberg delete failingasked a month ago
When querying athena using aws sdk, is it necessary to save results in s3?asked 8 days ago
Athena -- Query exhausted resources at this scale factorasked 3 years ago
How to query .json.gz files from Amazon Athena?Accepted Answerasked 3 years ago
Can I use the ID of my saved query to start query execution in Athena SDK?asked a month ago
AWS Data Wrangler Athena Query FailureAccepted Answerasked 6 months ago
Athena query failing while using UNNEST(x) AS yasked a month ago
Can i execute an Athena saved query from lambda?Accepted AnswerMODERATORasked 3 years ago
I cannot use current_date + interval in Athena boto3 query in LambdaAccepted Answerasked 4 months ago