Skip to content
Advertisement

Join on Id in Variant (Array) Snowflake

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'}

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement