How can I use a full-text search on a jsonb column with Postgres?

Question

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?

Answer 1

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.

Answer 2

PostgreSQL 11+

You can create a GiST or GIN index on jsonb columns with jsonb_to_tsvector('<language>', <jsonb_column>, <filter>) (or json columns with json_to_tsvector)

The filter can be any combination of '["string", "numeric", "boolean", "key", "all"]'. The first three are in regards to what kinds of values you want to include, while "key" includes all keys.

For example:

CREATE TABLE test (
  titles jsonb, 
  titles_tsvector tsvector generated always as(  
   jsonb_to_tsvector('english', titles, '["string"]')
  ) stored
)

See the docs and search for "jsonb_to_tsvector" (v13+) or "json(b)_to_tsvector" (v11, v12)

Answer 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