Postgres queries for JSON Array

Question

I have a table called cust_data which stores id and JSON object. I want to write postgres select statements to fetch:

1. select all id's where "gender": "Female" is not present in persons array [this should return id#3 from below data]
2. select all id's where "gender": "Female" is present and "status":"married" [this should return id#2 from below data]

Table : cust_data

id(numeric) | connections (jsonb)
------------------------------
1, {"Persons": [
 {
 "personName": "Tom",
 "gender": "Male",
 "country": "USA",
 "status":"single"
 },
 {
 "personName": "Harry",
 "gender": "Male",
 "country": "USA",
 "status":"single"
 },
 {
 "personName": "Lisa",
 "gender": "Female",
 "country": "Mexico",
 "status":"single"
 } 
 ]
 }
 2,{
 "Persons": [
 {
 "personName": "Lisa",
 "gender": "Male",
 "country": "UK",
 "status":"single"
 },
 {
 "personName": "Harry",
 "gender": "Male",
 "country": "USA",
 "status":"single"
 },
 {
 "personName": "Lisa",
 "gender": "Female",
 "country": "Mexico",
 "status":"married"
 } 
 ]
 }
 3,{
 "Persons": [
 {
 "personName": "Lisa",
 "gender": "Male",
 "country": "UK",
 "status":"single"
 },
 {
 "personName": "Harry",
 "gender": "Male",
 "country": "USA",
 "status":"single"
 } 
 ]
 }

• Fahad Anjum: have you looked on official postgres page: postgresql.org/docs/9.3/static/functions-json.html
• user842122: Yes. But it pull all records. Here is the query - SELECT id from cust_data t, jsonb_array_elements(t.connections->'persons') AS elem WHERE elem->>'gender' !='female'
• Fahad Anjum: please check persons. In your example it is capital letter. while in query it is written with small letter.

Answer 1

You can use boolean aggregate functions:

select id
from cust_data,
lateral jsonb_array_elements(connections->'Persons')
group by 1
having not bool_or(value->>'gender' = 'Female');
 id 
----
 3
(1 row)
select id
from cust_data,
lateral jsonb_array_elements(connections->'Persons')
group by 1
having bool_or(value->>'gender' = 'Female' and value->>'status' = 'married');
 id 
----
 2
(1 row)

Test it here.

---

If the arrays may be empty you should use left join ... on true instead of lateral. Add also coalesce() with appropriate default value for aggregates as they can yield null, e.g.:

select id
from cust_data
left join jsonb_array_elements(connections->'Persons') on true
group by 1
having not coalesce(bool_or(value->>'gender' = 'Female'), false);

• user842122: Thanks! this query is not fetching ids where persons array is empty

Answer 2

Query for 1:

WITH test AS (
 SELECT id, jsonb_array_elements(t.connections->'Persons') AS elem
 FROM cust_data t
), findFemale AS (
 SELECT DISTINCT id FROM test
 WHERE elem->>'gender' = 'Female'
) 
SELECT id FROM cust_data
WHERE id NOT IN (select * from findFemale)

Query for 2:

 WITH test as (SELECT id, jsonb_array_elements(t.connections->'Persons') AS elem 
 from cust_data t
 ) , findFemaleMarried as (
 select distinct id from test
 where 
 elem ->> 'gender' = 'Female' and elem ->> 'status' = 'married'
 )
 select * from findFemaleMarried

I hope above query will solve your problem.