Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table parking_lot 
(
  parking_lot_id     bigint,
  parking_lot_name   varchar(100)
);
create table parking_lot_vehicle_class_map 
(
  parking_lot_id         bigint,
  vehicle_class_id       bigint,
  available_slot_count   bigint
);
create table parking_slot 
(
  parking_lot_id          bigint,
  vehicle_class_id        bigint,
  parking_slot_id         bigint,
  slot_occupancy_status   bigint
);
insert into parking_lot (parking_lot_id, parking_lot_name)
values
( 1, 'PARKING 1' ),
( 2, 'PARKING 2' );
2 rows affected

insert into parking_lot_vehicle_class_map (parking_lot_id, vehicle_class_id, available_slot_count)
values
( 1, 1, 10 ),
( 1, 1, 11 ),
( 2, 2, 12 ),
( 2, 2, 13 );
4 rows affected

insert into parking_slot (parking_lot_id, vehicle_class_id, parking_slot_id, slot_occupancy_status) 
values
( 2, 1, 1, 1 ),
( 2, 1, 2, 1 ),
( 2, 2, 3, 0 );
3 rows affected

with details as (
  select jsonb_build_object('ParkingLotID', lot.parking_lot_id, 
                            'ParkingLotName', lot.parking_lot_name, 
                            'ParkingLotOccupancySummary', map."ParkingLotOccupancySummary") as summary
  from parking_lot lot
    left join (
      select map.parking_lot_id, 
             jsonb_agg(jsonb_build_object('VehicleClassID', map.vehicle_class_id, 
                                'AvailableSlotCount', map.available_slot_count)
                                ||case when slot.details is null then '{}'
                                       else jsonb_build_object('SlotOccupancyDetails', slot.details)
                                  end) as "ParkingLotOccupancySummary"
      from parking_lot_vehicle_class_map map
        left join (
          select parking_lot_id, vehicle_class_id, 
                 jsonb_agg(jsonb_build_object('SlotID', parking_slot_id, 'OccupancyStatus', slot_occupancy_status)) as details
          from parking_slot
          group by parking_lot_id, vehicle_class_id
        ) as slot on slot.parking_lot_id = map.parking_lot_id and slot.vehicle_class_id = map.vehicle_class_id
     group by map.parking_lot_id
    )  map on map.parking_lot_id = lot.parking_lot_id
)
select jsonb_build_object('ResHeader', jsonb_build_object('ResDate', current_timestamp, 'ResID', 1))||
       jsonb_build_object('ResDetail', jsonb_agg(d.summary))
from details d;


?column?


{"ResDetail": [{"ParkingLotID": 1, "ParkingLotName": "PARKING 1", "ParkingLotOccupancySummary": [{"VehicleClassID": 1, "AvailableSlotCount": 10}, {"VehicleClassID": 1, "AvailableSlotCount": 11}]}, {"ParkingLotID": 2, "ParkingLotName": "PARKING 2", "ParkingLotOccupancySummary": [{"VehicleClassID": 2, "AvailableSlotCount": 12, "SlotOccupancyDetails": [{"SlotID": 3, "OccupancyStatus": 0}]}, {"VehicleClassID": 2, "AvailableSlotCount": 13, "SlotOccupancyDetails": [{"SlotID": 3, "OccupancyStatus": 0}]}]}], "ResHeader": {"ResID": 1, "ResDate": "2021-01-26T12:25:50.154924+00:00"}}