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 22.214.171.124 (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
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.