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