I have ORACLE-SQL Table in below format.

Version is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

CREATE TABLE temp_Table 
   (
    "OVRPK_CNTNR_ID" CHAR(20) NOT NULL ENABLE, 
    "ITEM_DISTB_Q" NUMBER(3) NOT NULL ENABLE,
    "SSP_Q" NUMBER(6,0) NOT NULL ENABLE, 
    "BRKPK_CNTNR_ID" CHAR(20)
    ) ;


INSERT INTO temp_Table (OVRPK_CNTNR_ID, ITEM_DISTB_Q, SSP_Q,  BRKPK_CNTNR_ID ) VALUES('92000000356873110552', 6,8,'93058901021076000085');
  INSERT INTO temp_Table (OVRPK_CNTNR_ID,ITEM_DISTB_Q,SSP_Q,   BRKPK_CNTNR_ID ) VALUES('92000000356873110552',8, 10,'93058901021117700080');
  INSERT INTO temp_Table (OVRPK_CNTNR_ID,ITEM_DISTB_Q,SSP_Q,   BRKPK_CNTNR_ID ) VALUES('92000000356873110552', 10,2,'93058901022276900083');
  INSERT INTO temp_Table (OVRPK_CNTNR_ID,ITEM_DISTB_Q,SSP_Q,   BRKPK_CNTNR_ID ) VALUES('92000000334160826089', 3,4,'000000000083465239'); 
  INSERT INTO temp_Table (OVRPK_CNTNR_ID,ITEM_DISTB_Q,SSP_Q,   BRKPK_CNTNR_ID ) VALUES('92000000334160826089', 3,4,'000000000083438160');

I have written trigger for above table.

AFTER INSERT OR  DELETE OR  UPDATE OF 
OVRPK_CNTNR_ID, ITEM_DISTB_Q, SSP_Q,  BRKPK_CNTNR_ID
ON 
temp_Table 
FOR EACH ROW

DECLARE 
    json_message    CLOB := '';

BEGIN
    /*
     * pick OVRPK_CNTNR_ID from triggers row and send it to function createJson() return json object, example : 92000000356873110552
     */

    json_message = CALL FUNCTION createJson(:NEW.OVRPK_CNTNR_ID);

END;


CREATE OR REPLACE FUNCTION createJson(ovrpk_container_id IN char)

BEGIN
SELECT json_query(
                    json_objectagg(OVRPK_CNTNR_ID value 
                                    json_array(
                                                json_object("break_pack_container_id" VALUE BRKPK_CNTNR_ID,
                                                            "ssp_quantity" VALUE SSP_Q,
                                                            "item_distribution_quantity" VALUE ITEM_DISTB_Q)))),
                                                            
                                                            
 '$' returning VARCHAR2(4000) pretty )
    AS "Result JSON" 
  FROM temp_Table
END;

I am trying to create OUTPUT like below: but oracle version is Database 12c. I am not sure if it supports.

{
  "Over_pack_container_id":"92000000356873110552"
  "type": "OVER_PACK",
  "sub_containers": [
    {
      "break_pack_container_id": "93058901021076000085",
      "ssp_quantity": 8,
      "item_distribution_quantity": 6
    },    
    {
      "break_pack_container_id": "93058901021117700080",
      "ssp_quantity": 10,
      "item_distribution_quantity": 8
    },
    {
      "break_pack_container_id": "93058901022276900083",
      "ssp_quantity": 2,
      "item_distribution_quantity": 10
    }
  ]
}

can someone help me here? Thank you

CodePudding user response：

It is unclear what you are trying to achieve with the trigger or the function because the trigger does not do anything.

However, you can get your JSON output format using:

SELECT JSON_OBJECT(
         KEY 'Over_pack_container_id' VALUE OVRPK_CNTNR_ID,
         KEY 'type' VALUE 'OVER_PACK',
         KEY 'sub_containers' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'break_pack_container_id' VALUE BRKPK_CNTNR_ID,
             KEY 'ssp_quantity' VALUE SSP_Q,
             KEY 'item_distribution_quantity' VALUE ITEM_DISTB_Q
           )
         )
       ) AS "Result JSON" 
FROM  temp_Table
GROUP BY OVRPK_CNTNR_ID;

Which, for the sample data, outputs:

If you only want a single container ID in your function then use a where clause:

SELECT JSON_OBJECT(
         KEY 'Over_pack_container_id' VALUE OVRPK_CNTNR_ID,
         KEY 'type' VALUE 'OVER_PACK',
         KEY 'sub_containers' VALUE JSON_ARRAYAGG(
           JSON_OBJECT(
             KEY 'break_pack_container_id' VALUE BRKPK_CNTNR_ID,
             KEY 'ssp_quantity' VALUE SSP_Q,
             KEY 'item_distribution_quantity' VALUE ITEM_DISTB_Q
           )
         )
       ) AS "Result JSON" 
FROM  temp_Table
WHERE OVRPK_CNTNR_ID = ovrpk_container_id
GROUP BY OVRPK_CNTNR_ID;

fiddle