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!