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%"'
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With