postgresql - 如何为模糊和右 anchor 搜索索引 PostgreSQL JSONB 平面文本数组？

Question

PostgreSQL 版本:9.6。

events 表有一个 visitors JSONB 列:

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

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

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

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

是否可以索引数组的值以执行高效的模式匹配搜索:

1. 左锚定:选择访问者与“John%”匹配的事件
2. 右锚定:选择访问者与“%Doe”匹配的事件
3. unaccented:选择访客与“Frederic%”匹配的事件
4. 不区分大小写:选择访问者与“john%”匹配的事件

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

我读了Pattern matching on jsonb key/value和 Index for finding an element in a JSON array但提供的解决方案似乎不适用于我的用例。

Answer 1

您应该将 jsonb 转换为 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';

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