1

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 ?

Haleemur Ali
  • 26,718
  • 5
  • 61
  • 85
khilo
  • 3,793
  • 1
  • 12
  • 11
  • Actually , my question was not clear, my scenario is to compose a query using RLIKE for jsonb type coulmns.. – khilo Dec 26 '17 at 11:45

2 Answers2

2

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';
klin
  • 112,967
  • 15
  • 204
  • 232
  • Sorry for misleading , but i missed in my question the jsonb type , i have reformatted my question with that type ... – khilo Dec 26 '17 at 11:55
0

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"

khilo
  • 3,793
  • 1
  • 12
  • 11