Postgresql JSON之类的查询 [英] Postgresql json like query

问题描述

我有一个名为module_data的下表.当前它具有三行条目:

I have the following table called module_data. Currently it has three rows of entries:

                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运算符查询它.类似于以下内容:

Column data's datatype is jsonb. I'm trying to query it using like operator. Something like the following:

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

我一直在寻找jsonb支持，并且似乎没有like运算符.如果有人有任何建议.

I've been looking at the jsonb support and there doesn't seem to be a like operator. If anyone has any advice.

推荐答案

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

If the data column is text type, then use ->> on cast:

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

如果已经是json:

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

这篇关于Postgresql JSON之类的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！