Cross join flatten JSON - flatten only one the most recent row As reported in this question, i want to flatten a JSON starting from a nested JSON object that contains arrays like this one:
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;
...

It works, but it flattens all rows that were returned from the query: i need only to flatten the objects that are contained into the first, most recent row (ordered by insert time desc), but when in my clause i'll insert LIMIT 1 it will return only the first flattened json row, why? maybe this is a beginner question, but i'm not able to figure out!
This is the my actual query:
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;
Something like this (haven't tried it)
with my_mt as (
  select * from mytable
     where device_id = 20
     order by insert_time desc
     limit 1
) SELECT  my_mt.measurement_id,
          p.*
    from my_mt
    cross join jsonb_to_recordset(my_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);