0

I have a table master_data_approval_table in my postgres DB having field approval_value (type Jsonb) which contains below structure:

enter image description here

We want only those records where each object of jsonb array strictly matches the given condition "status": "Approved"

Expected O/P:

enter image description here

I tried with different queries but didn't find the exact query to get the expected output. Please help me with this.

Thanks in advance.

srp
  • 619
  • 7
  • 18

3 Answers3

2

First flatten the JSONB field and then do a routine select.

select mdat.* from master_data_approval_table mdat
cross join lateral jsonb_array_elements(approval_value) as l
group by id 
having count(*) filter (where l ->> 'status' = 'Approved') = count(*);

I assume that master_data_approval_table.id is the primary key. If not then replace select mdat.* with select mdat.id in the query above.
DB Fiddle.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21
1
SELECT approval_value
FROM master_data_approval_table
WHERE NOT EXISTS (
   SELECT 1
   FROM jsonb_array_elements(approval_value) arr
   WHERE arr->>'status' <> 'Approved'
);

The jsonb_array_elements function will expand the jsonb array into separate rows, and then you can filter those rows based on the condition.

This query will return the rows from the master_data_approval_table where all objects in the jsonb array have "status": "Approved".

mtoaima
  • 1
  • 2
0

Here's what you want:

SELECT * FROM master_data_approval_table WHERE (approval_value::jsonb)->>'status' = 'Approved'

For some reason if you try to select json_field->>'json_object' you get an error, so you need to (somewhat redundantly) cast your json field to json.

D.Tabakov
  • 131
  • 4