我可以使用Ilike检查JSONB中表的内容吗

发布于 2025-01-25 13:34:53 字数 252 浏览 2 评论 0原文

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

我尝试切换some_jsonb_table在文本上，但仍然Ilike不起作用。如何检查表的内容是否包含我要搜索的文本片段？

原文

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

I tried to switch some_jsonb_table on TEXT but still ILIKE doesn't work.
How to check if the content of the table contains the text fragment I am searching for ?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

酒与心事 2025-02-01 13:34:53

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上的好资源

jsonb_data::text ilike '%my_search_string% should work perfectly

Take this sample table and query, it works even on nested jsonb objects

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%';

Result is as expected

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

You can also use alternative operators

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';

Good resource on jsonb in PostreSQL

回复收藏 0 原文

~没有更多了~