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