Question: Search for a json array in jsonb column having data as json array in posgresql

Question

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

Answers 1
Added at 2016-11-16 08:11
Tags
Question

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...

Answers
nr: #1 dodano: 2016-11-17 08:11

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'
Source Show
◀ Wstecz