table 1:

table 2:

I need :

In Snowflake how to achieve this? I need to preserve order of strings as well. FLATTEN to split array into rows, joining to the other table and back to an array using array_agg doesn't preserve order of array elements.

CodePudding user response：

The required step is to use the flatten index column (which is the order of items in the array) as the ORDER BY for the array_agg

with table_1(id, arr) as (
    select column1, parse_json(column2) 
    from values
        (100, '["a","c","b"]'),
        (200, '["d","e","c","a"]')
), table_2(letter, id) as (
    select * from values
    ('a', 1),
    ('b', 2),
    ('c', 3),
    ('d', 4),
    ('e', 5)
)
select 
    t1.id
    ,array_agg(t2.id) within group (order by f.index) as id_arr
from table_1 as t1
    ,table(flatten(input=>t1.arr)) as f
join table_2 as t2
    on t2.letter = f.value
group by 1
order by 1;

gives: