CODEWITHSUNDEEP
arraysjsonpostgresqlpostgresql-9.1jsonb
user842122
user842122

Reputation: 295

Postgres queries for JSON Array

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

Upvotes: 1

Views: 1426

Answers (2)

Fahad Anjum
Fahad Anjum

Reputation: 1256

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.

Upvotes: 0

klin
klin

Reputation: 121604

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

Upvotes: 5

Related Questions