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

Question

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?

while it would be better to normalize your data, you can try https://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array — nbk, Jul 18 '23 at 17:21
Please give a concrete example of what you like or don't like about each option. — jjanes, Jul 18 '23 at 18:53

score 0 · Accepted Answer · answered Jul 19 '23 at 06:48

0

You can concatenate the results of different to_tsvector function calls:

to_tsvector(text_col) || to_tsvector(jsonb_col)

answered Jul 19 '23 at 06:48

Laurenz Albe

209,280
17
206
263

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

1 Answers1