Basically, I am trying to create a tag search system for images, in which each image has an array of tags within a jsonb field image_data, like so:
id | image_data |
---|---|
1 | {tags: ['landscape', 'dog']} |
2 | {tags: ['landscape', 'cat']} |
3 | {tags: ['landscape', 'cats']} |
The search consists of an array of tags and should return images that have all the searched tags. I did some experiments with the json operator ?&. The problem is that this operation does not support wildcard characters.
select id from images where (image_data ->'tags') ?& array['landscape', 'cat%']
The above query should return the following results:
id | image_data |
---|---|
2 | {tags: ['landscape', 'cat']} |
3 | {tags: ['landscape', 'cats']} |
Is there any way to create a query similar to this, but with wildcard character support?
2
Answers
You’re right in that the ?& operator doesn’t support wildcards, but there’s a way around that by using the unnest function, which will extract the elements of the array into a set of rows.
I think yu’re right that
?&
doesn’t support wildcards — you can go around it by using unnest functions that extract the elts from the array into a set of rows.LIKE
cat
You can do this by expanding a JSON array to a set of text values with
jsonb_array_elements_text
. then check that each image has at least the total number of searching tags (in this case we have 2):Result :
Demo here