JSON Creation Issue

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