0

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

3
  • Your desired output does not match provided sample data. – Akina Jan 26 at 10:52
  • Unrelated, but: please use integer or bigint to store numbers without decimals. Not NUMERIC(10) – a_horse_with_no_name Jan 26 at 11:26
  • This is sample structure and data. In actual I'll use appropriate data types and data accordingly. – Manoj Kumar Jan 26 at 12:18
0

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

1
  • Thanks a lot. This solution really helped. – Manoj Kumar Feb 1 at 5:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.