Join on Id in Variant (Array) Snowflake

Question

I have two tables t1 and t2 created as follows:

Create Tables

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())
)

Goal

I am looking to join t2 to t1 if the id of the record exists in the t1 variant array.

What I've Tried

I came across the ARRAY_CONTAINS function which looked perfect. But with the following I am receiving no results:

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

How Do I Get This?

Any suggestions on how to achieve the following result?

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

t2 records:
{id: 1, name: 'test'},
{id: 2, name: 'test2'}

Desired Result

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

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

score 2 · Answer 1 · answered Sep 27 '21 at 19:25

2

ARRAY_CONTAINS expects the following parameters: variant and array:

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

should be rather:

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

answered Sep 27 '21 at 19:25

Lukasz Szozda

162,964
23
234
275

Interestingly enough this still returns no rows. Do you think the fact that the ids in the id_array are enclosed in double quotes might effect anything? e.g. [ "1", "4" ] – T. Young Sep 29 '21 at 14:25

score 2 · Accepted Answer · answered Feb 10 '22 at 03:21

So to debug what's happening I added more and more line to the below SQL, but the short story is you have the parameters to ARRAY_CONTAINS back to front:

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

gives:

ID	NAME	ID	IDS_ARRAY	CONST_1	CONST_2	V1	IT_WORKS
1	test	1	[ "1", "2" ]	TRUE	TRUE	"1"	TRUE
1	test2	2	[ "1", "2" ]	TRUE	TRUE	"2"	TRUE

so now using the correct form:

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
FROM t1, t2 
WHERE ARRAY_CONTAINS(t2.id::text::variant, t1.ids_array)

we get:

ID	NAME
1	test
1	test2

Join on Id in Variant (Array) Snowflake

2 Answers2