Skip to content

how to Join array with string in Snowflake

I have two tables:

Books with column tag_ids (Array)

Tags with column tag_id (Varchar)

I have a visualisation tool that need to join these tables together. The only thing that I can control over is the join condition. the problem is that I can’t find a way to make this join work.

this is what I tried:

LEFT JOIN "PUBLIC"."TAGS" ON (  "tag_ids" =  TO_ARRAY("tag_id")) 

This works only on array with one element. It will not work for cases like:

tag_ids =


tag_id =


Tried to do:

LEFT JOIN "PUBLIC"."TAGS" ON ( ARRAYS_OVERLAP ("tag_ids" ,  TO_ARRAY("tag_id")) = TRUE ) ) 

I don’t know if this works but it does certisian product and takes 15+ to run so I can’t use this anyway.

What I’m looking for is basically to ask

if tag_id exist in tag_ids

I’m sure there is a simple solution for that.



You want to use the array_contains function.

LEFT JOIN "PUBLIC"."TAGS" ON (array_contains(tag_id, tag_ids))

See the docs here.

4 People found this is helpful