2021 – Simply Evolving JSON Structure (Updates)

JSON_TRANSFORM can ensure JSON structure when records are inserted into the database using an INSTEAD OF INSERT trigger on a view.

CREATE OR REPLACE TRIGGER instead_patient
  INSTEAD OF INSERT ON migrated_patient
  BEGIN
    INSERT INTO json_patient
    VALUES ( JSON_TRANSFORM(:new.migrated,
               SET '$.unique_os' =
                 OS_LIST_UNIQUE( JSON_QUERY(:new.migrated,'$.mrns[*].OS' WITH WRAPPER) )
                  REPLACE ON EXISTING,
               INSERT '$.mrns[*].sdate' = SYSDATE IGNORE ON EXISTING,
               INSERT '$.mrns[*].edate' = NULL IGNORE ON EXISTING ));
END;

Test it with an SQL Insert.

INSERT INTO migrated_patient
VALUES('{"pname":"Insert Trigger","mrns":[{"OS":1,"MRN":"MRN99"}]}');

Query the result from the database.

SELECT *
  FROM json_patient
  WHERE JSON_VALUE(patient_data,'$.pname') = 'Insert Trigger';

patient_data
------------------------------------------
{"pname":"Insert Trigger",
 "mrns":[{"OS":1,"MRN":"MRN99",
          "sdate":"2021-10-31T02:23:48",
          "edate":null}],
 "unique_os":1}

Thanks for reading!