12

So i have a jsonb column that has entries like this: https://pastebin.com/LxJ8rKk4

Is there any way to implement a full-text search on the entire jsonb column?

13

PostgreSQL 10+

PostgreSQL 10 introduces Full Text Search on JSONB

CREATE INDEX ON table
   USING gin ( to_tsvector('english',jsondata) );

The new FTS indexing on JSON works with phrase search and skips over both the JSON-markup and keys.

3

You can, although whether that's practical is not so clear:

CREATE TABLE t
(
    id SERIAL PRIMARY KEY,
    the_data jsonb
) ;

CREATE INDEX idx_t_the_data_full_text 
    ON t 
    USING gist ( (to_tsvector('English', the_data::text))) ;

And then query it with:

SELECT
    the_data
FROM
    t
WHERE
    to_tsvector('English', the_data::text) @@ plainto_tsquery('English', 'Action') ;

Note that this will also find all your object keys, not only the values. And you'll be limited to how much text

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.