Athena query failing while using UNNEST(x) AS y

0

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.

Thanks !!

質問済み 2年前1677ビュー
1回答
0

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.

エキスパート
回答済み 2年前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ