如何用另一个表的id替换字符串数组?

Question

表1:

<表类>id的arrtbody><<tr>100["a","c","b"]200["d","e","c","a"]

所需的步骤是使用flatten索引列(即数组中项的顺序)作为array_agg

的order BY

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;

给:

<表类>IDID_ARRtbody><<tr>100(1、3、2)200(4、5、3、1)