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?


1 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,
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;
  • 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?
    – VirtApp
    Commented Sep 3, 2020 at 14:16
  • p is the table alias for the result of jsonb_to_recordset(). p.* simply selects all columns from that "table"
    – user1822
    Commented Sep 3, 2020 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.