Transform Array in Snowflake Table Using Data From Different Table

user_19513131 0
Viewed 44

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?

snowflake-cloud-data-platform
2 Answers

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 ] ]
user_127563810

enter image description here

SELECT 
    CTE.ID
  , CTE.EMP  
  , ARRAY_AGG(ARRAY_CONSTRUCT(TABLE_B.NAME,CTE.LETS_KEEP )) NEW_PAYLOAD 
FROM 
    (SELECT GG.ID,G.VALUE[0] LETS_REPLACE, G.VALUE[1] LETS_KEEP,GG.EMP 
        FROM TABLE_A GG,LATERAL FLATTEN(INPUT=>EMP)G) CTE 
LEFT OUTER JOIN 
    TABLE_B ON TABLE_B.BUILD_ID = LETS_REPLACE
GROUP BY 
     1,2


courtesy of greg :
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');
user_13524900
Related