skip to Main Content

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


  1. 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.

    • unnest tags array
    • check each tag individually using LIKE
    • group by id and image_data (include groups where # of matched tags = # of search tags)
    • array_length(array[‘cat%’], 1) could be replaced with # of search tags
    SELECT id, image_data
    FROM (
        SELECT id, image_data, tag
        FROM images, unnest((image_data -> 'tags')::text[]) as tag
    ) s
    WHERE tag LIKE 'cat%'
    GROUP BY id, image_data
    HAVING count(*) = array_length(array['cat%'], 1);
    
    • returns images that have one or more tags that start with cat
    Login or Signup to reply.
  2. 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):

    select id, image_data
    from (
      select id, image_data, value as tag
      from images i, jsonb_array_elements_text(i.image_data -> 'tags')
    ) as s
    where tag = 'landscape' or tag like 'cat%'
    group by id, image_data
    having count(1) >= array_length(array['landscape', 'cat%'], 1)
    

    Result :

    id  image_data
    3   {"tags": ["landscape", "cats"]}
    2   {"tags": ["landscape", "cat"]}
    

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search