postgresql - LIKE query on elements of flat jsonb array

Question

I have a Postgres table posts with a column of type jsonb which is basically a flat array of tags.

What i need to do is to somehow run a LIKE query on that tags column elements so that i can find a posts which has a tags beginning with some partial string.

Is such thing possible in Postgres? I'm constantly finding super complex examples and no one is ever describing such basic and simple scenario.

My current code works fine for checking if there are posts having specific tags:

select * from posts where tags @> '"TAG"'

and I'm looking for a way of running something among the lines of

select * from posts where tags @> '"%TAG%"'

Answer 1

SELECT *
FROM   posts p
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements_text(p.tags) tag
   WHERE  tag LIKE '%TAG%'
   );

Related, with explanation:

• Search a JSON array for an object containing a value matching a pattern

Or simpler with the @? operator since Postgres 12 implemented SQL/JSON:

SELECT *
--     optional to show the matching item:
--   , jsonb_path_query_first(tags, '$[*] ? (@ like_regex "^ tag" flag "i")')
FROM   posts
WHERE  tags @? '$[*] ? (@ like_regex "TAG")';

The operator @? is just a wrapper around the function jsonb_path_exists(). So this is equivalent:

...
WHERE  jsonb_path_exists(tags, '$[*] ? (@ like_regex "TAG")');

Neither has index support. (May be added for the @? operator later, but not there in pg 13, yet). So those queries are slow for big tables. A normalized design, like Laurenz already suggested would be superior - with a trigram index:

• PostgreSQL LIKE query performance variations

For just prefix matching (LIKE 'TAG%', no leading wildcard), you could make it work with a full text index:

CREATE INDEX posts_tags_fts_gin_idx ON posts USING GIN (to_tsvector('simple', tags));

And a matching query:

SELECT *
FROM   posts p
WHERE  to_tsvector('simple', tags)  @@ 'TAG:*'::tsquery

Or use the english dictionary instead of simple (or whatever fits your case) if you want stemming for natural English language.

to_tsvector(json(b)) requires Postgres 10 or later.

Related:

• Get partial match from GIN indexed TSVECTOR column
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL