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