Postgresql – JSON Creation Issue

jsonpostgresql

I'm new to PostgreSQL database. Although I've good experience on Oracle Database. I'm facing issue while generating JSON from Postgres. I've tried to generate the same output but not able to achieve is properly. Can someone please help or guide may be with examples on similar problem. I'll be very much thankful to you.

"PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit"

Tables and Sample Data.

CREATE TABLE PARKING_LOT
(
      PARKING_LOT_ID      NUMERIC(10)
     ,PARKING_LOT_NAME    VARCHAR(100)
) ;

CREATE TABLE PARKING_LOT_VEHICLE_CLASS_MAP
(
      PARKING_LOT_ID       NUMERIC(10)
     ,VEHICLE_CLASS_ID     NUMERIC(10)
     ,AVAILABLE_SLOT_COUNT NUMERIC(10)
) ;


CREATE TABLE PARKING_SLOT
(
      PARKING_LOT_ID          NUMERIC(10)
     ,VEHICLE_CLASS_ID        NUMERIC(10)
     ,PARKING_SLOT_ID         NUMERIC(10)
     ,SLOT_OCCUPANCY_STATUS   NUMERIC(10)
) ;


INSERT INTO PARKING_LOT VALUES( 1, 'PARKING 1' ) ;
INSERT INTO PARKING_LOT VALUES( 2, 'PARKING 2' ) ;

INSERT INTO PARKING_LOT_VEHICLE_CLASS_MAP VALUES( 1, 1, 10 ) ;
INSERT INTO PARKING_LOT_VEHICLE_CLASS_MAP VALUES( 1, 1, 11 ) ;
INSERT INTO PARKING_LOT_VEHICLE_CLASS_MAP VALUES( 2, 2, 12 ) ;
INSERT INTO PARKING_LOT_VEHICLE_CLASS_MAP VALUES( 2, 2, 13 ) ;

INSERT INTO PARKING_SLOT VALUES( 2, 1, 1, 1 ) ;
INSERT INTO PARKING_SLOT VALUES( 2, 1, 2, 1 ) ;
INSERT INTO PARKING_SLOT VALUES( 2, 2, 3, 0 ) ;

Desired Output

{
   "ResHeader":{
      "ResDate":"09-01-2021 12:38:20",
      "ResID":"12345",
      "ResName":"Occupancy",
      "ResDesc":"Parking Lot Availability and Occupancy Status"
   },
   "ResDetail":[
      {
         "ParkingLotID":"1",
         "ParkingLotName":"PARKING 1",
         "ParkingLotOccupancySummary":[
            {
               "VehicleClassID":"1",
               "AvailableSlotCount":"10"
            },
            {
               "VehicleClassID":"2",
               "AvailableSlotCount":"12"
            }
         ]
      },
      {
         "ParkingLotID":"2",
         "ParkingLotName":"PARKING 2",
         "ParkingLotOccupancySummary":[
            {
               "VehicleClassID":"1",
               "AvailableSlotCount":"5",
               "SlotOccupancyDetails":[
                  {
                     "SlotID":"1",
                     "OccupancyStatus":"1"
                  },
                  {
                     "SlotID":"2",
                     "OccupancyStatus":"1"
                  }
               ]
            },
            {
               "VehicleClassID":"2",
               "AvailableSlotCount":"7",
               "SlotOccupancyDetails":[
                  {
                     "SlotID":"3",
                     "OccupancyStatus":"0"
                  }
               ]
            }
         ]
      }
   ]
}

What I'm able to achieve till now in bits and pieces

WITH
    HEAD AS
    (
        SELECT json_build_object( 'ResHeader', H ) HeaderData
        FROM
        (
            SELECT
                 TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.MS') "ResDate"
                ,1  "ResID"
        ) H
    )
SELECT *
FROM HEAD H

SELECT X.PARKING_LOT_ID, row_to_json(X)
FROM
(
    SELECT VCM.PARKING_LOT_ID, VCM.VEHICLE_CLASS_ID  "VehicleClassID"
    FROM PARKING_LOT_VEHICLE_CLASS_MAP2 VCM
    LEFT OUTER JOIN PARKING_SLOT2 PS
    ON VCM.PARKING_LOT_ID = PS.PARKING_LOT_ID
) X

Regards
Manoj

Best Answer

You need multiple levels of aggregation to achieve this.

Something along the lines:

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;

Online example

Related Question