Search code examples
postgresqlfull-text-search

How to create tsvector in PostgreSQL for a table which has jsonb and "normal" columns

I have a table which has "normal" text columns and a single jsonb column and I want to be able to have full text search on all of them.

I understand that I can pass jsonb column to normal to_tsvector function. However jsonb_to_tsvector function has some additional options which are usefule in my situation (for example to exclude keys). Is it possible to create tsvector which combines text columns and data from json without keys?

Solution

  • You can concatenate the results of different to_tsvector function calls:

    to_tsvector(text_col) || to_tsvector(jsonb_col)