PostgreSQL JSON 类似查询

Question

我有一个名为 module_data 的表格，目前它有三行条目:

                id                               data
0ab5203b-9157-4934-8aba-1512afb0abd0 {"title":"Board of Supervisors Meeting","id":"1i3Ytw1mw98"}
7ee33a18-63da-4432-8967-bde5a44347a0 {"title":"Board of Supervisors Meeting","id":"4-dNAg2mn6o"}
8d71ca35-74eb-4751-b635-114bf04843f1 {"title":"COPD 101", "id":"l9O0jCR-sxg"}

列数据的数据类型是 jsonb。我试图使用 like 操作符查询它。类似以下内容：

SELECT * FROM module_data WHERE title LIKE '%Board%';

我一直在研究jsonb支持，但似乎没有like操作符。如果有人有建议，请告诉我。

- adviner

---

存在like运算符，但不存在title列。它是data列的属性/特性，因此您需要提取其值进行比较：data ->> 'title'或data #>> ARRAY['title']。 - pozs

Answer 1

如果数据列是文本类型，则在转换时使用->>。

select * from module_data where data::json->>'title' like '%Board%'

如果它已经是Json格式：

select * from module_data where data->>'title' like '%Board%'

- Gurwinder Singh

---

太棒了，谢谢。这是我第一次使用jsonb，所以我非常感谢这个提示。 - adviner

提醒阅读此答案的人：使用查询参数来避免 SQL 注入。 - baijum

@baijum 什么是查询参数？ - jayarjo

Answer 2

我发现以下内容对于jsonb类型的列更加直观和易于理解：

select * from table_name
where 
column_name::text like '%Something%'

发现了一篇更多例子和实现的好文章：https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/

希望对您有所帮助！

- Deep Sehgal

---

这也将搜索键，因此在使用时要小心。只有在可以保证键不会有搜索词的情况下才使用此功能。 - user961954

如果您只想在值上搜索而不是在键上搜索，可以尝试以下内容：select * from table_name where array_to_string( array(select value from jsonb_each_text( )), ' ') like '%Something%' - jlfenaux

如此之好，适用于快速调试。 - Peter F

Answer 3

另一个选项是将该列转换为文本类型，这可能对其他人有足够的帮助。例如：

select * from module_data where data::text like '%Board%'

请注意，这将在整个json中进行搜索，只有在您可以保证其他字段不会出现问题的情况下才应使用。

- nevster

---

这也将搜索键，因此在使用时要小心。只有在您可以保证键不会有搜索词的情况下才使用此功能。 - user961954

我需要搜索完美的键，所以这对我来说非常好 :) - Titozzz

这对于一次性搜索来说非常好。+1 - Shautieh

Answer 4

我认为它应该是这样的。

select * from module_data where data->>'$."title"' like '%Board%'

然后它才对我起作用。

- Karansing