Athena: FULL JOIN unable to concat columns with array type data

0

I have two tables in Athena, both have the same schema (user_id - a string, items - an array of ASINs). One table records users in Week 1 and the other one in Week 2. Some users in both tables share the same user_id while the rest are different from each other. I want to do a full join of both tables on the user_id while the items should be joint as a new array.

I have the following sql:

with w1 as ( select * from "week1_events" limit 100 ), w2 as ( select * from "week2_events" limit 100 ), w12 as ( select COALESCE(w1.user_id, w2.user_id) as user_id, w1.items as items_left, w2.items as items_right from w1 full join w2 on w1.user_id = w2.user_id ) select user_id, items_left || items_right as items from w12

But "items_left || items_right" always generates empty array.

gefragt vor 10 Monaten43 Aufrufe
Keine Antworten

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen