Modeling JSON in Oracle Database Tables

Having an extra bit bucket in your tables is pretty easy now in Oracle Database 12c and higher. We offer native JSON support [JSON DOCS] – you can have a free form text column and store anything you want in it.

AND, we pretty much give you the full power of the SQL interface to those bit bucket items – pretty neat I think.

I’m still big fan of the relational paradigm, and I think data modeling is ALWAYS key to performance and flexbility for your database apps. So, how would one go about taking advantage of this in your Oracle SQL Developer Data Modeler designs?

CONSTRAINT XYZ123 CHECK (bit_bucket IS JSON)

That’s all you need – just add a column level check constraint.

Here’s how to do that in the modeler.

First off, build your table. Then you’re going to need to decide if you want to store your JSON in a VARCHAR2, CLOB, or BLOB. I’m going with CLOB because I’m not sure how big my bit bucket is going to get, and I prefer them to BLOBs.

That will bring up THIS dialog:

And now we get to input our CHECK constraint text:

Previewing our DDL shows this:

CREATE TABLE identity_example (
    id       INTEGER
        GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
    NOT NULL,
    text     CLOB,
    "date"   DATE
)
    LOGGING;
 
ALTER TABLE identity_example ADD CONSTRAINT text_is_json CHECK (
    text IS JSON
);
 
ALTER TABLE identity_example ADD CONSTRAINT identity_example_pk PRIMARY KEY ( id );

Wondering about the IDENTITY clause, and how to model that?

Make It So.

So I’m going to copy and paste that DDL to my SQL Developer worksheet window and run it. Table IDENTITY_EXAMPLE created. Table IDENTITY_EXAMPLE altered. Table IDENTITY_EXAMPLE altered…yada, yada, yada.

Now let’s go shove some data in.

Now let’s go query the thing.