create table the_table (
 id integer,
 name text,
 details jsonb
);

insert into the_table 
values
(1, 'one',   '{"x":101, "y":201, "z":false, "more":{"weight":23.5, "created":"2023-12-10"}}'),
(2, 'two',   '{"x":102, "y":202, "z":true, "more":{"weight":8.72}}'),
(3, 'three', '{"x":103, "y":203, "more":{"created":"2024-01-20"}}');

select id, 
       name, 
       details['x']::numeric as length, 
       details['y']::numeric as width, 
       details['z']::boolean as is_available,
       details['more']['weight']::numeric as weight,
       details['more']['created']::text::date as created
from the_table;

select id, 
       name,
       j.x as length,
       j.y as width,
       j.z as is_available,
       j.more['weight']::numeric as weight,
       j.more['created']::text::date as created
from the_table
 cross join lateral jsonb_to_record(details) as j(x numeric, y numeric, z boolean, more jsonb);

truncate the_table;
insert into the_table 
values
(1, 'one', '[{"x":101, "y":201, "z":false, "more":{"weight":23.5, "even_more":{"created":"2023-12-10"}}},
             {"x":102, "y":202, "z":true, "more":{"weight":8.72}},
             {"x":103, "y":203, "more":{"even_more":{"created":"2024-01-20"}}}]'
),
(2, 'two', '[{"x":111, "y":211, "z":true, "more":{"weight":123.5, "even_more":{"created":"2023-12-11"}}},
             {"x":112, "y":212, "z":false, "more":{"weight":18.72, "location_ids":[91, 92, 93]}},
             {"x":113, "y":233, "more":{"even_more":{"created":"2024-01-21"}}}]'
);

select id, 
       name, 
       jdata['x']::numeric as length, 
       jdata['y']::numeric as width,
       case when jdata['z'] is null then 'Do not count on it'
            else case when jdata['z']::boolean then 'Yes' else 'Unfortunately not' end
       end as available,
       jdata['more']['weight']::numeric as weight,
       jdata['more']['even_more']['created']::text::date as created,
       loc::integer as loc_id
from the_table 
 cross join lateral jsonb_array_elements(details) as jdata
 left  join lateral jsonb_array_elements_text(jdata['more']['location_ids']) as l(loc) on true
order by id, weight nulls first;

select id, 
       name, 
       x as length, 
       y as width, 
       case when z is null then 'Do not count on it'
            else case when z then 'Yes' else 'Unfortunately not' end
       end as available,
       more['weight']::numeric as weight,
       more['even_more']['created']::text::date as created,
       loc::integer as loc_id
from the_table 
 cross join lateral jsonb_to_recordset(details) as (x numeric, y numeric, z boolean, more jsonb)
 left  join lateral jsonb_array_elements_text(more['location_ids']) as l(loc) on true
order by id, weight nulls first;