我在设计一个JSON路径查询时遇到困难，想通过全文检索索引对其进行有效的调优，似乎这是一个并不难的用例。这个用例适用于PostgreSQL 14，但如果能提供任意版本的PostgreSQL的信息也会很有帮助。

给出如下表格

CREATE TABLE T (ID BIGINT NOT NULL, DATA JSONB);

其中 DATA 包含来自以下JSON的 JSONB

{
  "text" : [
    { "lang" : "en", "desc" : "English description" },
    { "lang" : "fr", "desc" : "Description en français" },
    { "lang" : "de", "desc" : "Deutsche Beschreibung" }
  ],
  "otherkey" : "doesn't matter"
}

如何创建一个索引并查询，在任意 text 数组元素中包含英文 desc，且与给定的搜索字符串匹配？

我可以像这样创建一个索引：

CREATE INDEX I ON T USING GIN (to_tsvector('english', data->'text'->0->>'desc'));

然后查询

SELECT *
FROM T
WHERE
to_tsvector('english', data->'text'->0->>'desc') @@ websearch_to_tsquery('english', 'whatever')

但这假设英文标题总是放在第一个 text 数组项中，而它也可能出现在任意项中，甚至可能缺失。我希望查询能够在任意 text 数组元素中，找到键名为 lang、值为 "en" 的 desc 条目；如果不可能，至少在第一个这样的数组元素中也行。

我尝试过

-- doesn't work: --
CREATE INDEX I ON T USING GIN (to_tsvector('english', jsonb_path_query_first(data->'text', '$[*].desc ( @ .. .lang = "en" )'));

但Postgres的 JSONPath实现并没有实现能够过滤前驱节点子节点的 .. 前驱段/选择器。

我也尝试过

-- doesn't work either: --
CREATE INDEX I ON T USING GIN (to_tsvector('english', jsonb_path_query(data->'text', '$[*].desc')));

（查询本身没有问题，返回三个规范化结果行）仅将所有 desc 文本作为英文进行索引，但Postgres输出 ERROR: set-returning functions are not allowed in index expressions。

解决方案

1. 你其实根本不需要考虑数组中的位置，也不需要对其进行展开。对数组访问命名字段等同于对其中的元素进行访问。
2. 过滤器在你放置 ? 的上下文/层级中起作用，而不会让目标路径进入括号内引用的内容。你可以在 .lang 上进行过滤，但随后仍然让目标路径在括号之后指向 .desc。

jsonb_path_query_first(data,'$.text?(@.lang=="en").desc')

你可以在这个测试的第二行看到一个匹配：[demo at db<>fiddle]

select to_tsvector('english',jsonb_path_query_first(data,'$.text?(@.lang=="en").desc'))
       @@ websearch_to_tsquery('english', 'whatever') as is_a_match
      ,jsonb_path_query_first(data,'$.text?(@.lang=="en").desc')
      ,*
from t limit 10;

为完整起见，这里给出索引和查询，并附有一个 explain analyze，以确认它确实在按预期使用了它（构建完表后别忘了 analyze 表）。

create index i on t using gin (
  to_tsvector('english', jsonb_path_query_first(data,'$.text?(@.lang=="en").desc')));

vacuum analyze t;

explain analyze verbose
select * from t
where to_tsvector('english', jsonb_path_query_first(data,'$.text?(@.lang=="en").desc'))
  @@ websearch_to_tsquery('english', 'whatever')

如果你记得始终使用一个额外条件，你也可以把它做成一个部分索引：

create index i on t using gin (
  to_tsvector('english', jsonb_path_query_first(data,'$.text?(@.lang=="en").desc')))
where data @? '$.text.lang=="en"';

之所以 array.key 的访问起作用，是因为 默认情况下JSONPath表达式使用 lax 模式:

Lax模式在JSON文档与路径表达式不符合预期架构时，便于匹配JSON文档与路径表达式。若一个操作数不符合某一操作的要求，它可以在执行操作前自动被包装成一个SQL/JSON数组，或通过将其元素转换为一个SQL/JSON序列来解包后再执行操作。此外，比较运算符在lax模式下会自动解包它们的操作数，因此你可以开箱即用地比较SQL/JSON数组。