Transform Array in Snowflake Table Using Data From Different Table

Question

I have these two tables:

Table_A
| ID |             EMP            |
|----|----------------------------|
| 1  | [[1,200], [4,100], [5,60]] |
| 2  | [[1,200], [2,90], [3,45]]  |
| 3  | [[2,250], [4,200], [5,100]]|

Table_B
| BUILD_ID |    NAME    |
|----------|------------|
| 1        |    DT_A    |
| 2        |    DT_B    |  
| 3        |    DT_C    |
| 4        |    DT_D    |
| 5        |    DT_E    |

I want to add a column to Table_A that is mapped from Table_B so that Table_A looks like this:

| ID |             EMP            |                   MAP                  |
|----|----------------------------|----------------------------------------|
| 1  | [[1,200], [4,100], [5,60]] | [[DT_A, 200], [DT_D, 100], [DT_E, 60]] |
| 2  | [[1,250]                   | [[DT_A, 250]]                          |
| 3  | [[2,250], [4,200], [5,100]]| [[DT_B, 250], [DT_D, 200], [DT_E, 100]]|

What is the query?

Answer 1

You can use flatten, array_construct, and array_agg to do this:

create or replace table TABLE_A(ID int, EMP array);
insert into TABLE_A select 1, parse_json('[[1,200], [4,100], [5,60]]');
insert into TABLE_A select 2, parse_json('[[1,200], [2,90], [3,45]]');
insert into TABLE_A select 3, parse_json('[[2,250], [4,200], [5,100]]');

create or replace table TABLE_B(BUILD_ID int, NAME string);
insert into TABLE_B (BUILD_ID, NAME) values
(1,'DT_A'),
(2,'DT_B'),
(3,'DT_C'),
(4,'DT_D'),
(5,'DT_E');

with F1 as
(
select ID, VALUE[0] as ID_MAP, VALUE[1] as EMP from TABLE_A, table(flatten(EMP))
), JOINED as
(
select ID, ID_MAP, EMP, BUILD_ID, NAME from F1
    inner join TABLE_B B
        on F1.ID_MAP = B.BUILD_ID
)
select   ID
        ,array_agg(array_construct(BUILD_ID, EMP)) within group (order by NAME) EMP
        ,array_agg(array_construct(NAME, EMP)) within group (order by NAME) MAP
from joined
group by ID
order by ID
;

If you want to change the order of the arrays, you can change the sort order of within group order by (NAME) to order by something else (provided it's in the array manipulations such as BUILD_ID - if the column is not in the array manipulations the sort will not be stable).

Output:

ID	EMP	MAP
1	[ [ 1, 200 ], [ 4, 100 ], [ 5, 60 ] ]	[ [ "DT_A", 200 ], [ "DT_D", 100 ], [ "DT_E", 60 ] ]
2	[ [ 1, 200 ], [ 2, 90 ], [ 3, 45 ] ]	[ [ "DT_A", 200 ], [ "DT_B", 90 ], [ "DT_C", 45 ] ]
3	[ [ 2, 250 ], [ 4, 200 ], [ 5, 100 ] ]	[ [ "DT_B", 250 ], [ "DT_D", 200 ], [ "DT_E", 100 ] ]