Can I use ilike to check the contents of a table in jsonb-CodePudding

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 ?

CodePudding user response：

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