Question 216219 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search