Does Postgres Support Rlike statment in jsonb columns type?

Question

Does PostgreSQL support MySQL 'RLIKE' / Regexp statement in jsonb columns type ?

for example, need a query which pull from the following table all ids which contains the value 'big':

| data                                    |  
| "id" :"bigData" , "content" : "aaa...." |  
| "id" : "biggerData, "content" : "bbb..."|  
| "id": "smallData", "content: "ddd......"|  



Select * from myTable where data Rlike ...

Is it Applicable ?

Answer 1

If the column contains valid json objects you can use the ->> operator and simple LIKE or ILIKE operator:

with my_table(data) as (
values
    ('{"id": "bigData" , "content": "aaa...."}'::jsonb),
    ('{"id": "biggerData", "content": "bbb..."}'),
    ('{"id": "smallData", "content": "ddd......"}')
)

select *
from my_table
where data->>'id' like '%big%';

                   data                    
-------------------------------------------
 {"id": "bigData", "content": "aaa...."}
 {"id": "biggerData", "content": "bbb..."}
(2 rows)    

In more complicated cases you can also use the regex pattern matching operator ~, e.g.:

select *
from my_table
where data->>'id' ~ 'big.*Data';

Answer 2

Thanks Klin, I have used the format of :

select * from my_table 
where data ->> 'id' ~* 'big'; 

--> This will search for all id`s which its value contains the sub string "big"