Joining arrays on internal(nested ) row ids

Question

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

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

Mikhail Berlyant · Accepted Answer · 2022-02-23 05:29:52Z

2

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

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

answered Feb 23, 2022 at 5:29

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Joining arrays on internal(nested ) row ids

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related