CODEWITHSUNDEEP
google-bigquery
alex
alex

Reputation: 1917

Joining arrays on internal(nested ) row ids

how can I join/sum up array values row by row (i.e. rows representing offset in the array) when joining two tables

i.e. given below two tables the output for row_id = 1 should be STRUCT("A",ARRAY[41,43,45]) i.e. array of sum of col1.val1 and col2.val2 values for each internal_id corresponding to index in each array

WITH table1 AS (
  SELECT 1 AS row_id, STRUCT("A" AS internal_id, ARRAY[1,2,3] as val1) AS col1  
),
table2 AS (
    SELECT 1 AS row_id, STRUCT("A" AS internal_id, ARRAY[40,41,42] as val2) AS col2  
)

EDIT: So I tried to do the below, un-nesting the arrays first, but the result is incorrect, i.e. it puts the resulting array at each index, i.e. having 9 rows instead of just 3

result

WITH table1 AS (
  SELECT 1 AS row_id, STRUCT("A" AS internal_id, ARRAY[1,2,3] as val1) AS col1  
),
table2 AS (
    SELECT 1 AS row_id, STRUCT("A" AS internal_id, ARRAY[40,41,42] as val2) AS col2  
),
table1_unnested as(
    select row_id, col1.internal_id, val1 from table1, unnest(col1.val1) as val1
),
table2_unnested as(
    select row_id, col2.internal_id, val2 from table2, unnest(col2.val2) as val2
)
select t1.row_id, t1.internal_id, ARRAY_AGG(t1.val1+t2.val2) as newval 
from table1_unnested as t1
join table2_unnested  as t2 using(row_id)
group by t1.row_id, t1.internal_id

Upvotes: 0

Views: 48

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173141

You were very close - see below correction

WITH table1 AS (
  SELECT 1 AS row_id, STRUCT("A" AS internal_id, ARRAY[1,2,3] as val1) AS col1  
),
table2 AS (
    SELECT 1 AS row_id, STRUCT("A" AS internal_id, ARRAY[40,41,42] as val2) AS col2  
),
table1_unnested as(
    select row_id, col1.internal_id, val1, offset from table1, unnest(col1.val1) as val1 with offset
),
table2_unnested as(
    select row_id, col2.internal_id, val2, offset from table2, unnest(col2.val2) as val2 with offset
)
select t1.row_id, t1.internal_id, ARRAY_AGG(t1.val1+t2.val2) as newval 
from table1_unnested as t1
join table2_unnested  as t2 using(row_id, offset)
group by t1.row_id, t1.internal_id

with output

enter image description here

As you can see - I added offset in five(5) places to fix your original query

Upvotes: 2

Related Questions