CODEWITHSUNDEEP
sqlarraysgoogle-bigqueryinner-join
Arsik36
Arsik36

Reputation: 327

Big Query: How to join 2 tables on user ID when 1 table contains an array of user ids?

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!

Upvotes: 1

Views: 1570

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173141

Below is for BigQuery Standard SQL

#standardSQL
SELECT team_id, 
  ARRAY_AGG(t2.user_id IGNORE NULLS) user_id,
  IFNULL(SUM(value), 0) value
FROM `project.dataset.table1` t, t.user_id AS user_id
LEFT JOIN `project.dataset.table2` t2
USING(user_id)
GROUP BY team_id

You can test, play with above using sample data similar to yours in question as in below example

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 1 team_id, [1, 2, 3] user_id UNION ALL
  SELECT 2, [4, 5, 6] UNION ALL
  SELECT 3, [7, 8, 9] UNION ALL
  SELECT 4, [10, 11, 12] UNION ALL
  SELECT 5, [13, 14]
), `project.dataset.table2` AS (
  SELECT 2 user_id, 10 value UNION ALL
  SELECT 3, 20 UNION ALL
  SELECT 5, 20 UNION ALL
  SELECT 7, 30 UNION ALL
  SELECT 9, 1 UNION ALL
  SELECT 12, 40 
)
SELECT team_id, 
  ARRAY_AGG(t2.user_id IGNORE NULLS) user_id,
  IFNULL(SUM(value), 0) value
FROM `project.dataset.table1` t, t.user_id AS user_id
LEFT JOIN `project.dataset.table2` t2
USING(user_id)
GROUP BY team_id

with output

enter image description here

Upvotes: 2

GMB
GMB

Reputation: 222632

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)

Upvotes: 1

Related Questions