0

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
...
"GigabitEthernet0/0", "", 2, "Not Connect", 1000000000, "1G"
"Bluetooth0/4", "", 2, "Not Connect", 1000000000, "1G"
...

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->'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;
1
  • It is the insert time column at row level: there is a pk - insert_time - data (json). sorry, i've missed that
    – VirtApp
    Sep 23, 2020 at 11:45

1 Answer 1

1

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->'ports')
                as p(port_name text,
                     port_description text,
                     port_status_int int,
                     port_status text,
                     port_speed_int int,
                     ports_speed text);
2
  • So, explaining this a little in depth, if i'm not wrong: you're creating a temp table, and after that you use this temp table to do the cross join?
    – VirtApp
    Sep 23, 2020 at 11:59
  • 1
    It's called a "common table expression" (postgresql.org/docs/current/queries-with.html), I don't think a temp table is involved, more like a view you define, but very good for performance if you need it more than once in the same query. Sep 23, 2020 at 13:18

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.