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?
Eg:
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)
Thanks!
Aron
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.
