how do I use jsonb_array_length with condition?-CodePudding

My table, t, has a nested jsonb_object, ss. Below is an example of a single record:

{
  "id":1,
  "ss":[
    { "ss_id":1, "approved":true },
    { "ss_id":2, "approved":false },
  ]
}

How can I count the number of objects in ss where approved = true?

I can get a count of all objects in ss using jsonb_array_length(), but have been unable to apply a filter to the count.

SELECT
  id,
  jsonb_array_length(t.ss) ss_cnt, -- returns total count
  jsonb_array_length(
    CASE WHEN t.ss -> 'approved' = 'true' THEN t.ss END
  ) a_cnt, -- returns null
  ss
FROM t

For context, my end goal is to create a bool indicator of whether all objects in ss have a value of "approved":true. This is the intermediate step to make that evaluation, but am open to other approaches.

CodePudding user response：

You need to unnest array elements with jsonb_array_elements():

select id, value
from t
cross join jsonb_array_elements(ss)
where (value->'approved')::bool

Use the boolean aggregate function bool_and to check whether all elements of an array has "approved": true

select id, bool_and((value->'approved')::bool)
from t
cross join jsonb_array_elements(ss)
group by id

Db<>Fiddle.

Read in the docs: