postgresql 对平面jsonb数组元素的LIKE查询

b91juud3 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(243)

我有一个Postgres表posts，它有一个jsonb类型的列，这基本上是一个平面标签数组。
我需要做的是以某种方式对tags列元素运行LIKE查询，这样我就可以找到一个以部分字符串开头的标签的帖子。
在Postgres中可以这样做吗？我不断地发现超级复杂的例子，从来没有人描述过如此基本和简单的场景。
我目前的代码可以很好地检查是否有特定标签的帖子：

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

字符串
我在寻找一种方法，在

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

型

postgresql

来源：https://stackoverflow.com/questions/50101530/like-query-on-elements-of-flat-jsonb-array

1条答案

按热度按时间

bwleehnv1#

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

字符串
相关，并附有说明：

在JSON数组中搜索包含与模式匹配的值的对象

或者使用**@?**操作符更简单，因为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")';

型
运算符@?只是函数jsonb_path_exists()的 Package 器。所以这是等价的：

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

型
也没有索引支持。（稍后可能会为@?运算符添加，但在第13页中还没有）。所以这些查询对于大表来说很慢。像Laurenz已经建议的标准化设计会上级-使用三元组索引：

PostgreSQL LIKE查询性能变化

如果只需要前缀匹配（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或更高版本。
相关信息：

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 对平面jsonb数组元素的LIKE查询

1条答案

相关问题

热门标签

最新问答