喜欢查询

我有一个名为 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操作员。如果有人有什么建议。

85304 次浏览

小开

最佳答案

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:

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

小开

I Think it should be like

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

then only it worked for me.

小开

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.

小开

I found the following is more straight-forward and easier for jsonb type of columns:

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/

Hope it helps!