Modeling JSON in Oracle Database Tables, Part 2

In case you haven’t figured this out by now, 90% of my material here is generated by questions from folks like you out there. So thanks for the never ending supply of content! And it gives me an opportunity to keep learning as well!

So the question:
I believe this json data type in MySQL would probably map to either CLOB or VARCHAR2 in Oracle. Is this something which can be achieved by defining custom data type mapping in sqldeveloper data modeler. Will there be additional impact of the dependent objects or code. Any suggestions on how to take care of this?

JSON in Oracle 12c

There’s no special data type today. You simply take for your VARCHAR2, CLOB, or BLOB column, and add a CHECK constraint. This is explained in the docs.

CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   po_document VARCHAR2 (23767)
   CONSTRAINT ensure_json CHECK (po_document IS JSON));

My advice: don’t use VARCHAR2. Eventually someone will want put a document in there that won’t fit.
Maria’s advice: consider BLOB over CLOB – it will be faster.

Back to the Data Modeler

The Manual Method
If you’re wondering why this is called ‘Part 2’ – it’s because I already showed how to add a CHECK CONSTRAINT for your JSON columns here.

But that seems like too many steps. And I really like JSON, and I’m going to be using it, a LOT. So how to make this easier?

The Easy/Fast Way: DOMAINS

Tools – Domain Administration.

Click the ADD button.

I give the DOMAIN a name: C-JSON. That’s how it will appear when I select it in the column data type properties.
I give it a logical type: CLOB. That’s how it will be generated in DDL that’s produced for my tables.
Check Constraint: For model type 12cR2, this text will get attached to the column.

%column% IS json

Click ok, and SAVE.

Repeat for B-JSON, and choose BLOB instead.

Click ok, and SAVE, and CLOSE.

Let’s go back into the modeler and add two more tables.

No more remembering the check constraint, no more remembering to use CLOB vs VARCHAR2.

Let’s look at the generated DDL:

CREATE TABLE table_with_binary_json (
  id      INTEGER
    GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
  NOT NULL,
  jsons   BLOB
)
LOGGING;
 
ALTER TABLE table_with_binary_json ADD CHECK ( jsons IS JSON );
 
ALTER TABLE table_with_binary_json ADD CONSTRAINT table_with_binary_json_pk PRIMARY KEY ( id );
 
CREATE TABLE table_with_char_json (
  id      INTEGER
    GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
  NOT NULL,
  jsons   CLOB
)
 
logging;
 
ALTER TABLE table_with_char_json ADD CHECK ( jsons IS JSON );
 
ALTER TABLE table_with_char_json ADD CONSTRAINT table_with_char_json_pk PRIMARY KEY ( id );
 
CREATE TABLE table_with_json (
  id      INTEGER
    GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 NOCACHE ORDER )
  NOT NULL,
  jsons   CLOB
)
 
logging;
 
ALTER TABLE table_with_json ADD CONSTRAINT ensure_json CHECK ( jsons IS JSON );
 
ALTER TABLE table_with_json ADD CONSTRAINT table_with_json_pk PRIMARY KEY ( id );

Note on Domains

Store them in a separate file. Source control them. Treat them like you would treat anything else that’s important to your projects. Heli has LOTS of advice on this in her Modeling book.