postgresql - 在 PostgreSQL GIN 索引中使用 JSON 对象值

Question

我想在我的 JSONB 对象 obj 中的所有值上创建一个 trgm GIN 索引。

我想出的第一个方法是这样的:

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

当然，以上是不行的，因为子查询(SELECT)不能用于PostgreSQL索引。

是否有另一种方法可以在不使用子查询的情况下连接 JSONB 对象的值？

Answer 1

您需要为此定义自定义函数，因为内置的 json[b] 函数都返回 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

有了这个，你可以创建一个索引，比如:

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

之后，您可以将此索引用于您的LIKE 和 FTS 查询:

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