Search code examples
postgresqlpostgresql-9.4jsonb

Search for a json array in jsonb column having data as json array in PostgreSQL

I have a jsonb column(gppermission) in my table doc which contains data as 

[{"Deny": "true", "Allow": "false", "GroupName": "Group 1 "}, 
 {"Deny": "false", "Allow": "true", "GroupName": "Group 2 "}, 
 {"Deny": "false", "Allow": "true", "GroupName": "Group 3 "}, 
 {"Deny": "true", "Allow": "false", "GroupName": "Group 4 "}]

I need to search inside the this data for 

{"Deny": "false", "Allow": "true", "GroupName": "Group 3 "}

I tried the below query. but no results :(

select * from doc as dc ,jsonb_array_elements(dc.gppermission) as e(gp) where e.gp = '{"Deny":"false","Allow":"true","GroupName":"Group 3"}'

Saw Query for array elements inside JSON type but it has an 'object' reference, my json array is different

Please help...

Solution

  • I got a solution, this may not be the only solution to do this.

    select * from doc as dc ,jsonb_array_elements(dc.gppermission) as e(gp) where e.gp ->>'Deny'='false' and e.gp ->>'Allow'='true' and e.gp ->>'GroupName'='Group 1'