PostgreSQL - Flatten nested JSONB object array 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 &quot;ports&quot; key, so, instead of having a json output i need to have them in a &quot;flattended table&quot; way like below
port_name, port_description, port_status_int, port_status, port_speed_int, ports_speed
...
&quot;GigabitEthernet0/0&quot;, &quot;&quot;, 2, &quot;Not Connect&quot;, 1000000000, &quot;1G&quot;
&quot;Bluetooth0/4&quot;, &quot;&quot;, 2, &quot;Not Connect&quot;, 1000000000, &quot;1G&quot;
...

Note that the lenght of the &quot;ports&quot; 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-&gt;'ports')::jsonb)-&gt;'port_name' as &quot;port_name&quot;,
(jsonb_array_elements(mytable.data-&gt;'ports')::jsonb)-&gt;'port_description' as &quot;port_description&quot;,
(jsonb_array_elements(mytable.data-&gt;'ports')::jsonb)-&gt;'port_status_int' as &quot;port_status_int&quot;,
(jsonb_array_elements(mytable.data-&gt;'ports')::jsonb)-&gt;'port_status' as &quot;port_status&quot;,
(jsonb_array_elements(mytable.data-&gt;'ports')::jsonb)-&gt;'port_speed_int' as &quot;port_speed_int&quot;,
(jsonb_array_elements(mytable.data-&gt;'ports')::jsonb)-&gt;'ports_speed' as &quot;ports_speed&quot;
from mytable where device_id = 20;

Any toughts?
Set returning functions should be called in the FROM.
SELECT mt.measurement_id,
       p.port -&gt;&gt; 'port_name' as &quot;port_name&quot;,
       p.port -&gt;&gt; 'port_description' as &quot;port_description&quot;,
       p.port -&gt;&gt; 'port_status_int' as &quot;port_status_int&quot;,
       p.port -&gt;&gt; 'port_status' as &quot;port_status&quot;,
       p.port -&gt;&gt; 'port_speed_int' as &quot;port_speed_int&quot;,
       p.port -&gt;&gt; 'ports_speed' as &quot;ports_speed&quot;
from mytable mt 
  cross join jsonb_array_elements(mt.data-&gt;'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-&gt;'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;