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"
            }   
          ]
        }

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);

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.