我可以使用like来检查jsonb表的内容吗?

Question

SELECT 
id,
some_jsonb_table
FROM
public.example
where some_jsonb_table::text ilike '%example_report%'

我试图在TEXT上切换some_jsonb_table，但ILIKE仍然不起作用。如何检查表的内容是否包含我正在搜索的文本片段?

Answer 1

jsonb_data::text ilike '%my_search_string%应该可以正常工作

以这个示例表和查询为例，它甚至可以在嵌套的jsonb对象上工作

with a as (
select cast('{"brand": "Toyota", "color": ["red", "black"], "price": 285000}' as jsonb) jsonb_data
union  
select cast('{"brand": "Honda", "color": ["blue", "pink"], "price": 25000}' as jsonb)
)
select * from a 
where jsonb_data::text ilike '%blue%';

结果如预期

jsonb_data
{"brand": "Honda", "color": ["blue", "pink"], "price": 25000}

也可以使用替代操作符

with a as (
select cast('{"brand": "Toyota", "color": ["red", "black"], "price": 285000, "sold": true}' as jsonb) jsonb_data
union  
select cast('{"brand": "Honda", "color": {"good":[["blue"], "pink"]}, "price": 25000, "sold": false}' as jsonb)
)
select * from a 
where jsonb_data ->> 'color' ~* 'blue';

PostreSQL中jsonb的好资源