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.

Open Data IS COOL!
Open Data IS COOL!

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!

about 290 out of 700k rows were causing my ADD CONSTRAINT to fail
about 290 out of 700k rows were causing my ADD CONSTRAINT to fail

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 🙂

Who wants to go through that much data looking for 'bad' records? Not me!
Who wants to go through that much data looking for ‘bad’ records? Not me!

JSON in My Schema

We make this very easy to find out, just query from USER_JSON_COLUMNS:

Just the one, for now.
Just the one, for now.

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%'
No fancy code required.
No fancy code required.

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.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

3 Comments

  1. Marcelo Carrasco Reply

    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

Write A Comment