sqlarraysjsonpostgresqlpostgresql-14

Strict condition check on each element of jsonb array postgres

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.

Solution 
  • 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".