There are somewhat similar answers already posted on StackOverflow, but they didn't address this specific case or involved a query that I was not able to understand, given that I just started my first SQL-related position.
This is the first time I try to join tables having a column values in one of the tables I am joining on in the form of an array. After trying to solve my own problem, I run into the following error: No matching signature for operator = for argument types: ARRAY<INT64>, STRING.
I have 2 tables that look like the following:
Table 1:
team_id user_id
1 [1, 2, 3]
2 [4, 5, 6]
3 [7, 8, 9]
4 [10, 11, 12]
Table 2:
user_id value
2 10
5 20
7 30
12 40
What I want to join Table 2 to Table 1 by way of having Table 2 analyze if there is a matching user_id in an array of Table 1. If there is, then join based on common user_id and output results as follows:
Desired Output
team_id user_id value
1 2 10
2 5 20
3 7 30
4 12 40
Thank you in advance for sharing your knowledge!
You can join on unnest():
select t1.team_id, t2.user_id, t2.value
from table1 t1
inner join table2 t2 on t2.user_id in unnest(t1.user_id)