Postgresql: How to perform LIKE query on a json array?

Question

I have a table with tags stored as a JSONB column. Like:

id   tags
---- ------------------------------
1    ["red", "blue"]
2    ["orange", "light pink"]
3    ["pink", "green"]

I'd like to select rows that contain tags that partially match a pattern -- in my case, I want tags with spaces in them. Something like SELECT * FROM the_table WHERE tags LIKE '% %'. Casting the array to text almost works, except everything matches because Postgres puts a space after commas in the output JSON.

Answer 1

Use the function jsonb_array_elements_text().

with my_table(id, tags) as (
values
    (1, '["red", "blue"]'::jsonb),
    (2, '["orange", "light pink"]'),
    (3, '["pink", "green"]')
)

select *
from my_table, jsonb_array_elements_text(tags)
where value like '% %';

 id |           tags           |   value    
----+--------------------------+------------
  2 | ["orange", "light pink"] | light pink
(1 row)