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:

FROM "PUBLIC"."BOOKS"
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 =

[
  "211d1383-13a6-4bef-85d1-bf5eda11c8a7",
  "2f6c332e-4726-4425-b364-e668e0f56582"
]

tag_id =

 ["211d1383-13a6-4bef-85d1-bf5eda11c8a7"]

Tried to do:

FROM "PUBLIC"."BOOKS"
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.

Answer

You want to use the array_contains function.

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

See the docs here.