up vote 1 down vote favorite

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"
            }   
          ]
        }
share|improve this question
    
have you looked on official postgres page: postgresql.org/docs/9.3/static/functions-json.html – Fahad Anjum May 10 at 19:33
    
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' ; – user842122 May 10 at 19:45
    
please check persons. In your example it is capital letter. while in query it is written with small letter. – Fahad Anjum May 10 at 19:50
up vote 5 down vote

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);
share|improve this answer
    
Thanks! this query is not fetching ids where persons array is empty – user842122 May 10 at 21:13
    
See the edited answer. – klin May 10 at 21:28
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.