Keep JSON in oracle database

Question

I have some JSON data in Java and I want to keep in an oracle database. I want to keep it in one specific field because it will not be queried. What oracle datatype should be used for that column to store JSON format? Should i convert it to string and keep it as that or is there something specific for JSON in oracle.

Answer 1

If it is JSON then it is a string. So I would consider the standard Oracle text types for storing it, for example CLOB like the comments suggested. Also the official Oracle documentation of storing JSON data in Oracle can come handy.

Answer 2

Oracle Database supports JSON data natively with relational database features, including transactions, indexing, declarative querying, and views. Unlike XML data, which is stored using SQL data type XMLType, JSON data is stored in Oracle Database using SQL data types VARCHAR2, CLOB, and BLOB. When possible, Oracle recommends that you use BLOB storage. In particular, doing so obviates the need for any character-set conversion (see JSON: Character Sets and Character Encoding in Oracle Database).

Maximum size of a VARCHAR2 or NVARCHAR2 column is only 4kb. Under 12c, if you have the MAX_STRING_SIZE server property set to EXTENDED, this limit can be increased to 32kb, but still nowhere near 150kb.

Using IS JSON in a Check Constraint to Ensure JSON Data is Strictly Well-Formed (Standard)

CREATE TABLE DTI_REPORT
   (id          RAW (16) NOT NULL,
    date_loaded TIMESTAMP WITH TIME ZONE,
    dti_document CLOB
    CONSTRAINT ensure_json CHECK (dti_document IS JSON (STRICT)));


INSERT INTO DTI_REPORT VALUES (
          SYS_GUID(),
          SYSTIMESTAMP,
          '{"Person"           : {"name":"Musa","age":45},
            "Report"           : "DTI0001",
            "Loans"            : {"loan1":{...},"loan2":{...}},
            "Salary"           : 4000,
            "DTI"           : "30%"}'
);