Trigram index on all values of a JSONB column

Question

I have a JSONB column that contains a bunch of key/value pairs, no complicated nested structures or arrays in there. The keys are dynamic, and I don't know them in advance. I'm using PostgreSQL 9.6.

What I'd like to do is to query the values in that JSONB column with a query like

ILIKE '%mysearchterm%'

which is very well supported by a trigram index. I've used them to index regular text columns, and it works very nicely for this kind of query, but I'm not sure how exactly to approach the JSONB column here.

Is there a way to dump all values (not the keys) from a JSONB column into a trigram index?

Answer 1

You can create a function which returns the data as a string:

CREATE OR REPLACE FUNCTION public.jsonb_val_to_string(jsonb)
 RETURNS text
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare x text;
BEGIN
  select string_agg(value,' ') into x from jsonb_each_text($1);
  return x;
END
$function$

And then create the index on that:

create index on jason using gin (jsonb_val_to_string(x) gin_trgm_ops);

And then query also using the function:

explain select * from jason where jsonb_val_to_string(x) ilike '%foo%';

Of course you might want to change the delimiter in string_agg to something you know will never be included in the ilike pattern.