将一个表中的外键数组与另一个表中的主键匹配?

Question

我有两个表。像这样的一个：

id   | array_of_ids
----------------------
001 | {012, 345, 789}
002 | {789, 123, 456}
003 | {234, 789, 567}
004 | {543, 210, 789}

另一个像这样：

ids  | str
-------------
012 | am_name1
345 | name2
789 | name3
123 | am_name4
456 | name5
234 | name6
567 | am_name7
543 | am_name8
210 | name9

我想创建一个如下所示的表：

id   | array_of_ids    | label
-----------------------------
001 | {012, 345, 789} | name1
002 | {789, 123, 456} | name4
003 | {234, 789, 567} | name7
004 | {543, 210, 789} | name8

我知道填充哪个标签看起来是随机的，但这里有更多细节：每个 id 都有一个相应的名称，但我只对某些类型的名称感兴趣——带有前缀"am"的名称。我希望能够扫描array_of_ids，检查数组中的 id 是否与我感兴趣的str匹配，并使用相应的名称填充一个新变量label。我希望这是清楚的！如有必要，很乐意编辑！

Answer 1

unnest()和join：

select t1.id, t1.array_of_ids,
max(case when regexp_like(t2.name, '^am_') then substr(t2.name, 4) end)
from table1 t1 cross join
unnest(t1.array_of_ids) t1_id(id) join
table2 t2
on t2.id = t1_id.id
group by t1.id, t1.array_of_ids;

Answer 2

我意识到我没有使用PRESTO的数组函数。此解决方案有点笨拙，因为它要求您在解决方案之外查找每个 id/名称对，但它有效。

SELECT id, array_of_ids,
CASE WHEN CONTAINS(array_of_ids, 012) = TRUE THEN 'name1'
WHEN CONTAINS(array_of_ids, 123) = TRUE THEN 'name4'
WHEN CONTAINS(array_of_ids, 567) = TRUE THEN 'name7'
WHEN CONTAINS(array_of_ids, 543) = TRUE THEN 'name8'
ELSE NULL END AS label
FROM table1