PostgreSQL JSONB 按单词查找文档

Question

我有一个表格：

                          Table "public.items"
     Column      |           Type           |         Modifiers         
-----------------+--------------------------+---------------------------
 item_id         | character varying        | not null
 content         | character varying        | 
 tags            | jsonb                    | 
 body            | jsonb                    | 

body 字段包含如下数据：

[{"text": "As a child home ..."}, 
{"text": "Great post. Watch this }]

在文本部分中按单词查找文档需要什么查询？

Answer 1

标准(也是最充分(的方法是由@Dan在另一个答案中提供的，即将 jsonb 列视为这样使用运算符 -> 和 ->> 解析它。

另一种方法是将列转换为文本并从那里开始，例如 body::text ：

SELECT * FROM public.items WHERE body::TEXT LIKE '%child%';
SELECT * FROM public.items WHERE body::TEXT SIMILAR TO '%child%';

如果您不希望它区分大小写，请在比较它们之前转换两个字符串，例如使用 LOWER 或 UPPER(请参阅文档(：

SELECT * FROM items WHERE LOWER(body::TEXT) LIKE LOWER('%CHILD%');

正如 Dan 在评论中建议的那样，ILIKE 是处理不区分大小写的查询的另一种优雅方法：

SELECT * FROM items WHERE body::TEXT ILIKE '%child%'

注意：这两种方法都依赖于适当的索引才能令人满意地工作，否则查询可能会变得非常慢。用GIN index查看另一个答案

Answer 2

您可以使用 jsonb 函数进行搜索。假设你有这个：

item_id | body
--------+---------------------------------------------------------------
1       | [{"text":"aSdf aSdf"},{"text":"12 41f"},{"text":"1 31s sf"}]
2       | [{"text":"31fa3"},{"text":"3f43f"}]
3       | [{"text":"l8412"},{"text":"52%$3d1f"},{"text":"agasd as3"}]
4       | [{"text":"i8i23"}]

您可以使用以下命令搜索 jsonb 中的每个元素：

SELECT * FROM (SELECT t.id,elem.*
FROM public.items t,jsonb_array_elements(t.body) AS elem)json_vals
WHERE value->>'text' ILIKE '%s%'

这将返回下一个结果，因为查询使用 ILIKE ：

item_id | value
--------+----------------------
1       | {"text":"aSdf aSdf"}
1       | {"text":"1 31s sf"}
3       | {"text":"agasd as3"}

如果只需要 id，则可以更改查询中的*以进行item_id并使用 DISTINCT 。但是通过此查询，您可以访问记录中的每个 JSON，而不必将其转换为 text 。

注意：如果您只是查询SELECT t.id,elem.* FROM public.items t,jsonb_array_elements(t.body) AS elem则会获得一行中的每个元素：

itme_id | value
--------+--------------------
1       | {"text":"aSdf aSdf"}
1       | {"text":"12 41f"}
1       | {"text":"1 31s sf"}
2       | {"text":"31fa3"}
2       | {"text":"3f43f"}
3       | {"text":"l8412"}
3       | {"text":"52%$3d1f"}
3       | {"text":"agasd as3"}
4       | {"text":"i8i23"}