对平面jsonb数组的元素进行类似查询 [英] LIKE query on elements of flat jsonb array

问题描述

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

I have a Postgres table posts with a column of type jsonb which is basically a flat array of tags.

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

What i need to do is to somehow run a LIKE query on that tags column elements so that i can find a posts which has a tags beginning with some partial string.

在Postgres中有这种可能吗?我一直在寻找超复杂的例子，没有人描述这种简单的基本情况.

Is such thing possible in Postgres? I'm constantly finding super complex examples and no one is ever describing such basic and simple scenario.

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

My current code works fine for checking if there are posts having specific tags:

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

并且我正在寻找一种在各行之间运行某项内容的方法

and I'm looking for a way of running something among the lines of

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

推荐答案

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

相关，并附有说明:

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

这不能使用任何索引.大桌子太慢了.标准化的设计像Laurenz已经建议的会更好-带有三字母索引:

This cannot use any indexes. So slow for big tables. A normalized design, like Laurenz already suggested would be superior - with a trigram index:

• PostgreSQL LIKE查询性能差异

仅需前缀匹配(LIKE 'TAG%'，无前导通配符)，就可以使其与全文本索引一起使用:

For just prefix matching (LIKE 'TAG%', no leading wildcard), you could make it work with a full text index:

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(或任何适合您的情况).

Or use the english dictionary instead of simple (or whatever fits your case) if you want stemming for natural English language.

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

相关:

• 从GIN索引的TSVECTOR中获得部分匹配列
• 模式与LIKE，SIMILAR TO或PostgreSQL中的正则表达式匹配

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

这篇关于对平面jsonb数组的元素进行类似查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！