Using JSON object values in PostgreSQL GIN index

Question

I want to create a trgm GIN index on all the values in my JSONB object obj.

The first approach I came up with is this:

CREATE INDEX table_values_idx ON table
    USING GIN (array_to_string(
        (SELECT value FROM jsonb_each_text(obj)), ' ') gin_trgm_ops);

Of course, the above doesn't work, because subqueries (SELECT) can not be used in PostgreSQL indices.

Is there another way to join the values of a JSONB object without the use of a subquery?

Answer 1

You will need to define your custom function for this, because the built-in json[b] functions all return setof sometype.

create or replace function jsonb_values(jsonb)
  returns text array
  language sql
  immutable
as $func$
  select case jsonb_typeof($1)
    when 'null'   then array[]::text[]
    when 'object' then array(select v from jsonb_each($1) e, unnest(jsonb_values(e.value)) v)
    when 'array'  then array(select v from jsonb_array_elements($1) e, unnest(jsonb_values(e)) v)
    else array(select v from jsonb_build_array($1) a, jsonb_array_elements_text(a) v)
  end
$func$;

http://rextester.com/GSTHZ3132

With this, you could create an index, like:

CREATE INDEX table_values_idx ON table
  USING GIN (array_to_string(jsonb_values(obj), ' ') gin_trgm_ops);

After that, you can use this index for your LIKE and FTS queries:

select *
from   table
where  array_to_string(jsonb_values(obj), ' ') like '%abc%'