I have two tables:
Books with column
Tags with column
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:
[ "211d1383-13a6-4bef-85d1-bf5eda11c8a7", "2f6c332e-4726-4425-b364-e668e0f56582" ]
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.
You want to use the
FROM "PUBLIC"."BOOKS" LEFT JOIN "PUBLIC"."TAGS" ON (array_contains(tag_id, tag_ids))
See the docs here.