Hi All,
I’m facing an issue while generating JSON from MariaDB.
CREATE TABLE `OCCUPANCY_DTLS` (
`PARKING_ID` INT(10) UNSIGNED NULL DEFAULT NULL,
`PARKING_LOT_ID` INT(10) UNSIGNED NOT NULL,
`VEHICLE_CLASS_ID` INT(10) UNSIGNED NOT NULL,
`TOTAL_SLOTS_AVAILABLE` INT(10) UNSIGNED NOT NULL,
`TOTAL_SLOTS_OCCUPIED` INT(10) UNSIGNED NOT NULL,
`ONLINE_BOOKING_ENABLED` INT(10) UNSIGNED NOT NULL,
`ONLINE_BOOKING_PERCENTAGE` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`PARKING_LOT_ID`, `VEHICLE_CLASS_ID`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
INSERT INTO OCCUPANCY_DTLS VALUES( 1, 1, 1, 100, 20, 1, 10 ) ;
INSERT INTO OCCUPANCY_DTLS VALUES( 1, 1, 2, 80, 10, 1, 10 ) ;
INSERT INTO OCCUPANCY_DTLS VALUES( 1, 2, 1, 100, 15, 1, 20 ) ;
SELECT
JSON_OBJECT
(
'ResHeader',
JSON_OBJECT
(
'ResDate', DATE_FORMAT(SYSDATE(), '%d-%m-%Y %H:%i:%s'),
'ResID', 1,
'ResName', 'Occupancy',
'ResDesc', 'Parking Lot Availability and Occupancy Status'
),
'ResDetail',
JSON_ARRAY(
JSON_OBJECT
(
'ParkingLotID', P.PARKING_LOT_ID,
'ParkingLotOccupancySummary', JSON_ARRAYAGG(
JSON_OBJECT
(
'VehicleClassID', P.VEHICLE_CLASS_ID,
'TotalSlots', P.TOTAL_SLOTS_AVAILABLE
)
ORDER BY P.VEHICLE_CLASS_ID
)
)
)
)
FROM occupancy_dtls P
GROUP BY P.PARKING_LOT_ID ;
Required Output :
{
"ResHeader":{
"ResDate":"09-01-2021 12:38:20",
"ResID":"12345",
"ResName":"Occupancy",
"ResDesc":"Parking Lot Availability and Occupancy Status"
},
"ResDetail":[
{
"ParkingLotID":"1",
"ParkingLotOccupancySummary":[
{
"VehicleClassID":"1",
"TotalSlots":"15"
},
{
"VehicleClassID":"2",
"TotalSlots":"15"
}
]
},
{
"ParkingLotID":"2",
"ParkingLotOccupancySummary":[
{
"VehicleClassID":"1",
"TotalSlots":"15"
}
]
}
]
}
Thanks & Regards
Manoj