Join on Id in Variant (Array) Snowflake

Question

我创建了两个表t1和t2，如下所示:

创建表

CREATE TABLE t1(
id integer AUTOINCREMENT START 1 INCREMENT 1,
name varchar(10) DEFAULT RANDSTR(10, random()),
id_array variant,
) 
CREATE TABLE t2(
id integer AUTOINCREMENT START 1 INCREMENT 1,
name varchar(10) DEFAULT RANDSTR(10, random())
)

如果记录的id存在于t1变量数组中，我希望将t2连接到t1。

What I've try

我遇到了ARRAY_CONTAINS函数，它看起来很完美。但是我没有收到任何结果:

SELECT t1.id, t2.name
FROM t1, t2 
WHERE ARRAY_CONTAINS(t1.id_array, array_construct(t2.id))

我怎么得到这个?

对于如何达到以下结果有什么建议吗?

t1 record: 
{id: 1, name: 'A-123', ids_array: '["1", "2"]'}
t2 records:
{id: 1, name: 'test'},
{id: 2, name: 'test2'}

预期的结果

{id: 1, name: 'A-123', joined_name: 'test'},
{id: 1, name: 'A-123', joined_name: 'test2'}

Answer 1

因此，为了调试发生的事情，我在下面的SQL中添加了越来越多的行，但简短的故事是，您有ARRAY_CONTAINS的参数从后往前:

WITH t1(id, name, ids_array) AS (
SELECT column1, column2, SPLIT(column3,',') FROM VALUES
(1, 'A-123', '1,2')
), t2(id, name) AS (
SELECT column1, column2 FROM VALUES
(1, 'test'),     
(2, 'test2')
)
SELECT t1.id
,t2.name
,t2.id
,t1.ids_array
,ARRAY_CONTAINS('1'::variant, t1.ids_array) as const_1
,ARRAY_CONTAINS('2'::variant, t1.ids_array) as const_2
,t2.id::text::variant as v1
,ARRAY_CONTAINS(v1, t1.ids_array) as it_works
FROM t1, t2 

给:

<表类>ID名称IDIDS_ARRAYCONST_1CONST_2V1IT_WORKStbody><<tr>1测试1("1","2";真正]对"1">对1test22("1","2";真正]对"2">对

ARRAY_CONTAINS期望以下参数:变量和数组:

SELECT t1.id, t2.name
FROM t1, t2 
WHERE ARRAY_CONTAINS(t1.id_array, array_construct(t2.id))

应该是:

SELECT t1.id, t2.name
FROM t1
JOIN t2 
ON ARRAY_CONTAINS(t2.id::variant, t1.id_array)