Querying JSONB array value for sub values?

Question

I have a JSONB Object:

{"name": "Foo", "interfaces": [{"name": "Bar", "status": "up"}]}

It is stored in a jsonb column of a table

create table device (
  device_name character varying not null,
  device_data jsonb not null
);

So i was trying to get a count by name of devices which have interfaces that are not 'up'. Group By is used for developing counts by naame, but i am having issues querying the json list for values.

MY first Attempt was:

select device_name, count(*) from device where device_json -> 'interfaces' -> 'status' != 'up' group by device_name;

Some surrounding data that made me think something was going to be difficult was:

select count(device_data -> 'interfaces') from device;

which I thought that was going to get me a count of all interfaces from all devices, but that is not correct. It seems like it is just returning the count from the first item.

Im thinking I might need to do a sub query or join of inner content.

Ive been thinking it over and when looking up psql it seems like i havent found a way to query a list type in a jsonb object. Maybe im mistaken. I didnt want to build a business layer on top of this as I figured that the DBMS would be able to handle this heavy lifting.

I saw there is a function jsonb_array_elements_text(device_data -> 'interfaces')::jsonb -> 'status' which would return the information, but I cant do any sort of count in it, as count(jsonb_array_elements_text(device_data -> 'interfaces')::jsonb -> 'status') will return ERROR: set-valued function called in context that cannot accept a set

Answer 1

You need a lateral join to unnest the array and count the elements that are down (or not up)

select d.device_name, t.num_down
from device d
  cross join lateral (
     select count(*) num_down
     from jsonb_array_elements(d.device_data -> 'interfaces') as x(i)
     where i ->> 'status' = 'down'
  ) t

To count all interfaces and the down interfaces, you can use filtered aggregation:

select d.device_name, t.*
from device d
  cross join lateral (
     select count(*) as all_interfaces,
            count(*) filter (where i ->> 'status' = 'down') as down_interfaces
     from jsonb_array_elements(d.device_data -> 'interfaces') as x(i)
  ) t

Online example

Answer 2

jsonb_array_elements is the right idea, I think you are looking for an EXISTS condition to match your description "devices which have interfaces that are not 'up'":

SELECT device_name, count(*)
FROM device
WHERE EXISTS (SELECT *
  FROM jsonb_array_elements(device_json -> 'interfaces') interface
  WHERE interface ->> 'status' != 'up')
GROUP BY device_name;

I would like to know how many interfaces are down

That's a different problem, for this you could use a subquery in the SELECT clause, and probably wouldn't need to do any grouping:

SELECT
  device_name,
  ( SELECT count(*)
    FROM jsonb_array_elements(device_json -> 'interfaces') interface
    WHERE interface ->> 'status' != 'up'
  ) AS down_count
FROM device