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 |