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

What if your array contains multiple interface names? E.g. two that are up and three that are down? — user330315
– user330315, Commented Feb 6, 2020 at 16:48
I am trying to get a count of 'down' essentially. So for each row/device, I would know how many interfaces are down. — Fallenreaper
– Fallenreaper, Commented Feb 6, 2020 at 16:49
So if one device has two interfaces up and three down you want that device listed with a count of three? — user330315
– user330315, Commented Feb 6, 2020 at 16:53
You are correct. I dont know if it would be more difficult to do: device_name, count(ALL INTERFACES), count(DOWN INTERFACE based on STATUS property), but at min, you are correct. — Fallenreaper
– Fallenreaper, Commented Feb 6, 2020 at 16:53

score 2 · Accepted Answer · 2020-02-06 17:01:21Z

2

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

edited Feb 6, 2020 at 17:01

answered Feb 6, 2020 at 16:55

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Fallenreaper Over a year ago

I was thinking a join and unnest here would likely be the be best course of action. Is there a timer within the DBMS, i want to test runtimes of yours vs Bergi's

user330315 Over a year ago

@Fallenreaper: compare the results of explain (analyze) select .... But bergi's second statement is essentially doing the same thing.

Fallenreaper Over a year ago

Out of curiosity, what is the use-case for if device_data is saved as an empty object? It looks like it is a soft error when accessing the interfaces property and would act as if the array is of size 0?

Fallenreaper Over a year ago

They are very similar indeed. Looks like the difference over the dataset i have is only 6.5ms. Fantastic

user330315 Over a year ago

@Fallenreaper: not sure what you mean, nothing changes if the JSON is empty: dbfiddle.uk/…

Bergi · Accepted Answer · 2020-02-06 19:55:34Z

1

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

edited Feb 6, 2020 at 19:55

answered Feb 6, 2020 at 16:57

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

2 Comments

Fallenreaper Over a year ago

My goal is on a per device basis. It looks like the exists command will assume plausibly discern that if a device has ANY non-up status interfaces that it should be reported. My goal is to get a total list of devicenames and the count of the interfaces under each device which aren't up. If that makes sense? Granted. Having a select which will return a list of devices which have at least 1 down interface sounds also very useful

Fallenreaper Over a year ago

Is there a built in DBMS too to time selects? now(): [select]; now() or something?

Collectives™ on Stack Overflow

Querying JSONB array value for sub values?

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related