count jsonb array with condition in postgres

Question

I have a postgres database where some column data are stored as follow:

guest_composition	charging_age
[{"a": 1, "b": 1, "c": 1, "children_ages": [10, 5, 2, 0.1]}]	3
[{"a": 1, "b": 1, "c": 1, "children_ages": [2.5, 1, 4]}]	3

i want to go over the children_ages array and to return the count of children that are above the age of 3. I am having a hard time to use the array data because it is returns as jsonb and not int array.

the first row should return 2 because there are 2 children above the age of 3. The second row should return 1 because there is 1 child above the age of 3.

I have tried the following but it didn't work:

WITH reservation AS (SELECT jsonb_array_elements(reservations.guest_composition)->'children_ages' as children_ages, charging_age FROM reservations

SELECT (CASE WHEN (reservations.charging_age IS NOT NULL AND reservation.children_ages IS NOT NULL) THEN SUM( CASE WHEN (reservation.children_ages)::int[] >=(reservations.charging_age)::int THEN 1 ELSE 0 END) ELSE 0 END) as children_to_charge

Answer 1

You can extract an array of all child ages using a SQL JSON path function:

select jsonb_path_query_array(r.guest_composition, '$[*].children_ages[*] ? (@ > 3)')
from reservations r;

The length of that array is then the count you are looking for:

select jsonb_array_length(jsonb_path_query_array(r.guest_composition, '$[*].children_ages[*] ? (@ > 3)'))
from reservations r;

It's unclear to me if charging_age is a column and could change in every row. If that is the case, you can pass a parameter to the JSON path function:

select jsonb_path_query_array(
          r.guest_composition, '$[*].children_ages[*] ? (@ > $age)', 
          jsonb_build_object('age', charging_age)
       )
from reservations r;