5

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%"'
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
mbajur
  • 4,406
  • 5
  • 49
  • 79
  • have you considerd something like `select * from posts where wmi_codes::text LIKE '%TAG%'`? – Jim Jones Apr 30 '18 at 13:27
  • i did and that's my plan B but it kind of feels like i'm dropping all the pros of using jsonb column with this solution instead of storing it just as a text column...am i ? – mbajur Apr 30 '18 at 13:28
  • I totally agree with you, it's not elegant at all. It does feel strange to use such an approach, but if it does what you want and there is no performance implication, I would still consider it. Good luck :-) – Jim Jones Apr 30 '18 at 13:31
  • 2
    If you want to process data efficiently in PostgreSQL, don't store them as JSON. If it is a list of tags, store them in a table. Then everything will become simple. – Laurenz Albe Apr 30 '18 at 13:33
  • 1
    i kind of need to use jsonb, that was not my decision nor my code and i need to adapt :) – mbajur Apr 30 '18 at 13:34

1 Answers1

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

Related, with explanation:

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:

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228