Working on some cool demos around JSON, ORDS, Oracle Database, JavaScript apps, etc. and I needed to load up a BUNCH of data to play with.
Note the ‘other_tags’ column LOOKS like JavaScript Object Notation (JSON).
There are JSON standards – should the key name and value strings be double or single quoted, etc. To ID text as JSON in Oracle it needs to pass our test.
Also, you need Oracle 12c, specifically 12.1.0.2 (and a patch) to start using this new feature.
Well, my data wasn’t passing the test.
Error starting at line : 1 IN command - ALTER TABLE opendata ADD CONSTRAINT is_json CHECK (other_tags IS JSON) enable Error report - SQL Error: ORA-02293: cannot validate (HR.IS_JSON) - CHECK CONSTRAINT violated 02293. 00000 - "cannot validate (%s.%s) - check constraint violated" *Cause: an ALTER TABLE operation tried TO validate a CHECK CONSTRAINT TO populated TABLE that had nocomplying VALUES. *Action: Obvious
Crap.
The action is obvious, fix those non-complying strings, or get rid of them.
If only there were an easy way to see them!
Ah, but there is:
SELECT * FROM OPENDATA WHERE other_tags IS NOT JSON;
I want to say thanks to Beda for making this easy to figure out from his blog post!
Many of these failures are obvious, a few strings aren’t starting with a quote, double or single.
Instead of fixing them, I just nuked them from the dataset. I can live with just 747,607 Rows 🙂
JSON in My Schema
We make this very easy to find out, just query from USER_JSON_COLUMNS:
And now I can query it.
I can use SQL to reach into the JSON and pull out specific keys by name.
SELECT name, json_value(other_tags, '$.religion') FROM opendata WHERE LOWER(name) LIKE '%church%'
There’s a LOT, LOT, LOT more going on here with this new JSON in the Database feature to talk about. As I learn it, I’ll share it here. I just really appreciated having the IS NOT JSON predicate available to weed out and ID my bad records so I could get my constraint added to my table.
3 Comments
Hi Jeff,
How are you doing???
I have a question regarding JSON in Oracle Database….is it possible to know what “library o method” Oracle Db uses to validate a JSON document????
I’m asking because, I have a java process that extracts data from an XML file and creates a JSON doc that is inserted into a table, but for some reason, some of those documents are not inserted due to a “IS JSON” constraint, but https://jsonlint.com/ says that is a valid json file
You need to know what the rules are, our rules that is. Here’s what the Docs say. Or, try asking Beda.
Thank Jeff.