JSON创作问题

Question

我是PostgreSQL数据库的新手。虽然我在Oracle数据库方面有很好的经验。我在从Postgres生成JSON时遇到了问题。我试图生成相同的输出，但无法正确地实现。是否有人可以帮助或指导可能与类似的问题的例子。我会非常感谢你的。

"PostgreSQL 12.4 on x86_64-pc-linux-gnu，由gcc (GCC) 4.8.3 20140911 (Red 4.8.3-9)，64位“编写

表和示例数据.

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 ) ;

期望输出

{
   "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"
                  }
               ]
            }
         ]
      }
   ]
}

到目前为止，我能够做到的一切，都是零碎的，

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

问候Manoj

Answer 1

为了实现这一点，您需要多个级别的聚合。

一些类似的东西：

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;

在线示例