PostgreSQL – Flatten nested JSONB object array

jsonpostgresql

Im my database with Postgres 11, i have this JSON in my rows stored in a JSONB field, and i need to flatten out the list of objects that are over "ports" key, so, instead of having a json output i need to have them in a "flattended table" way like below

port_name, port_description, port_status_int, port_status, port_speed_int, ports_speed
...
"GigabitEthernet0/0", "", 2, "Not Connect", 1000000000, "1G"
"Bluetooth0/4", "", 2, "Not Connect", 1000000000, "1G"
...

Note that the lenght of the "ports" object is not static and may vary.

As far i know, this it my solution…i have some doubts: is this solution ok? it can be a bottleneck? it can be optimized?

SELECT  mytable.measurement_id,
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_name' as "port_name",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_description' as "port_description",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_status_int' as "port_status_int",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_status' as "port_status",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_speed_int' as "port_speed_int",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'ports_speed' as "ports_speed"
from mytable where device_id = 20;

Any toughts?

Best Answer

Set returning functions should be called in the FROM.

SELECT mt.measurement_id,
       p.port ->> 'port_name' as "port_name",
       p.port ->> 'port_description' as "port_description",
       p.port ->> 'port_status_int' as "port_status_int",
       p.port ->> 'port_status' as "port_status",
       p.port ->> 'port_speed_int' as "port_speed_int",
       p.port ->> 'ports_speed' as "ports_speed"
from mytable mt 
  cross join jsonb_array_elements(mt.data->'ports') as p(port)
where device_id = 20;

You can simplify this a bit, by using jsonb_to_recordset:

SELECT  mt.measurement_id,
        p.*
from mytable mt 
  cross join jsonb_to_recordset(mt.data->'ports') as p(port_name text, port_description text, port_status_int int, port_status text, port_speed_int int, ports_speed text) 
where device_id = 20;

Related Solutions

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

PostgreSQL filtering on array length inside JSON

Without knowing your data, I can only guess that the selectivity of your index is low (which happen if the length of the array does not vary much).

One trick to overcome this might be changing the query slightly and creating a covering index. For this, choose a NOT NULL column (for example, the primary key of the table) to count, and then include this column in the index:

CREATE INDEX n_items ON your_table (jsonb_array_length(data->'items'), id);

SELECT count(id) 
  FROM your_table
 WHERE JSONB_ARRAY_LENGTH(table.data->'items') = 2;

This will hopefully turn into an index-only scan (I tested this omitting the jsonb part, but you will be able to tell if it works).

Best Answer

Related Solutions

Postgresql – find all JSON scalars in PostgreSQL 9.3

PostgreSQL filtering on array length inside JSON

Related Question