如何为 PostgreSQL JSONB 平面文本数组建立索引以进行模糊和右锚定搜索？

PostgreSQL 版本：9.6。

The events表有一个visitorsJSONB 列：

CREATE TABLE events (name VARCHAR(256), visitors JSONB);

The visitors列包含一个“平面”JSON 数组：

["John Doe","Frédéric Martin","Daniel Smith",...].

The events表包含 1000 万行，每行有 1 到 20 个访问者。

是否可以对数组的值进行索引以执行有效的模式匹配搜索：

1. 左锚定：选择访问者与“John%”匹配的事件
2. 右锚定：选择访问者与“%Doe”匹配的活动
3. 不带重音的：选择访问者与“Frederic%”匹配的活动
4. 不区分大小写：选择访问者与“john%”匹配的事件

我知道 Postgres trigram 扩展的存在gin_trgm_ops能够为不区分大小写和右锚定搜索创建索引，但我不知道如何为“平面”JSON 数组的内容创建三元组索引。

I read jsonb 键/值的模式匹配 and 用于在 JSON 数组中查找元素的索引但提供的解决方案似乎不适用于我的用例。

你应该投jsonb to text并在其上创建一个三元组索引：

CREATE EXTENSION pg_trgm;
CREATE INDEX ON events USING gin
   ((visitors::text) gin_trgm_ops);

然后对该列使用正则表达式搜索。例如，要搜索John Doe， 您可以使用：

SELECT ...
FROM events
WHERE visitors::text *~ '\mJohn Doe\M';

三元组索引将支持此查询。