arrays - LIKE 查询平面 jsonb 数组的元素

Question

我有一个 Postgres 表 posts，其中有一列类型为 jsonb，它基本上是一个平面标签数组。

我需要做的是以某种方式对该 tags 列元素运行 LIKE 查询，以便我可以找到一个标签以部分字符串开头的帖子。

这种事情在 Postgres 中可能吗？我一直在寻找 super 复杂的示例，但从来没有人描述过如此基本和简单的场景。

我当前的代码可以很好地检查是否有具有特定标签的帖子:

select * from posts where tags @> '"TAG"'

我正在寻找一种在以下行中运行某些东西的方法

select * from posts where tags @> '"%TAG%"'

Answer 1

SELECT *
FROM   posts p
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements_text(p.tags) tag
   WHERE  tag LIKE '%TAG%'
   );

相关，有解释:

• Search a JSON array for an object containing a value matching a pattern

或使用 @? operator 更简单自 Postgres 12 实现 SQL/JSON 以来:

SELECT *
--     optional to show the matching item:
--   , jsonb_path_query_first(tags, '$[*] ? (@ like_regex "^ tag" flag "i")')
FROM   posts
WHERE  tags @? '$[*] ? (@ like_regex "TAG")';

运算符@? 只是function jsonb_path_exists() 的包装器.所以这是等价的:

...
WHERE  jsonb_path_exists(tags, '$[*] ? (@ like_regex "TAG")');

两者都没有索引支持。 (稍后可能会为 @? 运算符添加，但在第 13 页中还没有)。所以这些查询对于大表来说很慢。标准化设计，like Laurenz already suggested会更好 - 使用三元组索引:

• PostgreSQL LIKE query performance variations

对于前缀匹配(LIKE 'TAG%'，无前导通配符)，您可以使用全文索引 :

CREATE INDEX posts_tags_fts_gin_idx ON posts USING GIN (to_tsvector('simple', tags));

和一个匹配的查询:

SELECT *
FROM   posts p
WHERE  to_tsvector('simple', tags)  @@ 'TAG:*'::tsquery

或者，如果您想提取自然英语语言，则使用 english 词典而不是 simple(或任何适合您的情况)。

to_tsvector(json(b))需要 Postgres 10 或更高版本。

相关:

• Get partial match from GIN indexed TSVECTOR column
• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL