PostgreSQL - Flatten nested JSONB object array

Question

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?

Answer 1

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;

• VirtApp: The second solution, using the jsonb_to_recordset method is simple but impressive! the only question i have is about this notation: p.* - what does this mean?
• user1822: p is the table alias for the result of jsonb_to_recordset(). p.* simply selects all columns from that "table"