Postgresql json 喜欢查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42918348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql json like query
提问by adviner
I have the following table called module_data. Currently it has three rows of entries:
我有下表称为 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"}
Column data's datatype is jsonb. I'm trying to query it using likeoperator. Something like the following:
列数据的数据类型是jsonb. 我正在尝试使用like运算符查询它。类似于以下内容：
SELECT * FROM module_data WHERE title LIKE '%Board%';
I've been looking at the jsonbsupport and there doesn't seem to be a likeoperator. If anyone has any advice.
我一直在寻找jsonb支持，似乎没有like运营商。如果有人有任何建议。
回答by Gurwinder Singh
If the data column is text type, then use ->>on cast:
如果数据列是文本类型，则在转换时使用->>：
select * from module_data where data::json->>'title' like '%Board%'
If it's already json:
如果它已经是 json：
select * from module_data where data->>'title' like '%Board%'
回答by Deep Sehgal
I found the following is more straight-forward and easier for jsonb type of columns:
我发现以下内容对于 jsonb 类型的列更直接、更容易：
select * from table_name
where
column_name::text like '%Something%'
Found a good article on more examples and implementations: https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/
找到了一篇关于更多示例和实现的好文章：https: //www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/
Hope it helps!
希望能帮助到你！
回答by nevster
One other option which may be sufficient for other people who've found this page is to just cast the column to text type. Eg
对于找到此页面的其他人来说，另一种可能足够的选项是将列转换为文本类型。例如
select * from module_data where data::text like '%Board%'
Note though, this will search over the entire json and should only be used if you can guarantee the other fields won't be a problem.
但请注意，这将搜索整个 json，并且只有在您可以保证其他字段不会出现问题时才应使用。
回答by Karansing
I Think it should be like
我认为它应该像
select * from module_data where data->>'$."title"' like '%Board%'
then only it worked for me.
那么只有它对我有用。
