Finding results whose array contains values of another array

Hi Team, got an array query question.

Say I have a record with a ‘tags’ field that is an array of strings, how I can find all records that have values within another array?

ID, Name, Tags

1, “James”, [“Tag A”, “Tag B”, “Tag C”]
2, "Frank [“Tag B”]
3, “Samantha”, [“Tag A”]
4, “Jane”, [“Tag C”]

How can I find all records who have tags [“Tag A”, “Tag C”]? (Would expect records 1, 3 and 4)


1 Like

Hi @aron !

Thanks for reaching out. Would something like Array_Contains work?

Hi @nadine,
From what I can read, ARRAY_CONTAINS only works for finding a single element in an array, which isn’t the above use case. I need to know if any values in an array are contained in an array.

Hi @aron -

Does something like this work? If not, please elaborate more on what you are looking for. In this example, it returns the right records on what you’re expecting. [TLDR: I did a typo on the name, but the records are correct).

also Array_intersect works:

with tags_id as (
  SELECT ARRAY_INTERSECT(tags, ['tag-a', 'tag-c']) as tags_wanted, id, name
FROM commons.CommunityQuestion c
select tags_wanted, id, name
from tags_id
where length(tags_wanted) != 0

Thanks for the array_intersect example. The query will be generated at run time so this is what we’d need. I’ll have a play and let you know how I go.

1 Like