Oracle #JSON check constraint #quicktip

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

This entry was posted in Database Stuff and tagged , , , by rlockard. Bookmark the permalink.

About rlockard

Robert Lockard is a professional Oracle Designer, Developer and DBA working in the world of financial intelligence. In 1987 his boss called him into his office and told him that he is now their Oracle Wizard then handed him a stack of Oracle tapes and told him to load it on the VAX. Sense then, Robert has worked exclusively as an Oracle database designer, developer and Database Administrator. Robert enjoys flying vintage aircraft, racing sailboats, photography, and technical diving. Robert owns and fly’s the “Spirit of Baltimore Hon” a restored 1948 Ryan Navion and lives in Glen Burnie Maryland on Marley Creek

Welcome to oraclewizard