I came across an interesting problem, we are using JSON in one of our applications and have no control over the people sending us the JSON data. This has caused some issues with data quality. One thing we needed to do is to check that all the required fields have a value when the data is loaded. I found solving the problem to be quite simple by adding check constraints on the JSON column.
alter table payload add constraint
payload_first_name_check check
(json_exists(json_document, ‘$.first_name));
alter table payload add constraint
payload_last_name_check check
(json_exists(json_document, ‘$.last_name’));
Now when the documents load, if the first or last name are null, the check constraint is violated and ORA-02290 will be raised.
Failed to execute: soda insert payload /home/oracle/data/04.json
java.sql.SQLIntegrityConstraintViolationException: ORA-02290: check constraint (RLOCKARD.SYS_C0012523) violated