json - Postgres 对 json 字段的全文搜索

Question

我不明白为什么索引有效，但我得到的结果集是空的。

https://www.db-fiddle.com/f/n9SyXK6GY3va2CZ41jNGQ5/2

我有 table :

create table content
(
    id bigserial not null constraint content_pk primary key,
    created_at timestamp with time zone not null,
    form json not null
);

字段表单以以下格式存储数据:

{
  "title_text": "Test test",
  "content": {
    "blocks": [
      {
        "key": "6j131",
        "text": "Lorem ipsum dolor sit amet,"
      },
      {
        "key": "6nml9",
        "text": "In tincidunt tincidunt porttitor."
      }
    ],
  }
}

我尝试创建索引以根据 title_text 和所有节点 content->blocks[]->text 的连接值进行搜索。

我的查询:

(函数样本 https://www.facebook.com/afiskon 谢谢你)

CREATE OR REPLACE FUNCTION make_tsvector(title TEXT, content json)
  RETURNS tsvector AS
'
BEGIN
    RETURN (setweight(to_tsvector(''simple'', title), ''A'')
    || setweight(to_tsvector(''simple'', STRING_AGG(content ->> ''text'', '' '')), ''B''));
END
'
    LANGUAGE 'plpgsql' IMMUTABLE;

(创建索引查询)

DROP INDEX IF EXISTS idx_content__form__title_text_and_block_text;
CREATE INDEX IF NOT EXISTS idx_content__form__title_text_and_block_text
  ON content
    USING GIST (make_tsvector(
                            content.form ->> 'title_text',
                            content.form -> 'content' -> 'blocks'
                    ));

(并使用 EXPLAIN 检查我的查询)

EXPLAIN
  SELECT c.id, c.form ->> 'title_text'
  FROM content c,
     json_array_elements(c.form -> 'content' -> 'blocks') block
  WHERE make_tsvector(
                  c.form ->> 'title_text',
                  c.form -> 'content' -> 'blocks'
          ) @@ to_tsquery('ipsum')
  GROUP BY c.id;

我看到索引有效(!)

HashAggregate  (cost=15.12..15.15 rows=2 width=40)
Group Key: c.id
->  Nested Loop  (cost=4.41..14.62 rows=200 width=64)
    ->  Bitmap Heap Scan on content c  (cost=4.41..10.62 rows=2 width=64)
          Recheck Cond: (make_tsvector((form ->> 'title_text'::text), ((form -> 'content'::text) -> 'blocks'::text)) @@ to_tsquery('ipsum'::text))
          ->  Bitmap Index Scan on idx_content__form__title_text_and_block_text  (cost=0.00..4.40 rows=2 width=0)
                Index Cond: (make_tsvector((form ->> 'title_text'::text), ((form -> 'content'::text) -> 'blocks'::text)) @@ to_tsquery('ipsum'::text))
    ->  Function Scan on json_array_elements block  (cost=0.01..1.01 rows=100 width=0)

但如果我使用此查询，我将得到空结果。

是不是索引构建函数调用STRING_AGG的问题？

Answer 1

在此处仔细查看这段代码。

make_tsvector(
  c.form ->> 'title_text',
  c.form -> 'content' -> 'blocks'
)

您没有选择您的想法。

c.form -> 'content' -> 'blocks'

返回一个 JSON 数组，而不是单个元素。另一方面，在您的函数中，您有这个(为清楚起见删除了转义引号):

content ->> 'text'

您传入的 JSON 不是对象；它是一个对象数组。因此查找失败，因为路径查询错误。

规划器报告您的索引正在被使用的原因是因为索引和您的查询都指向同一个无效路径。由于它们匹配，因此使用索引。但这并不意味着索引包含有用的信息。

找到一种在函数中或在调用函数的查询中遍历数组的方法，它应该开始工作。