12. Using JSON Data

Oracle Database 12.1.0.2 introduced native support for JSON data. You can use JSON with relational database features, including transactions, indexing, declarative querying, and views. You can project JSON data relationally, making it available for relational processes and tools. Also see node-oracledb’s SODA API, which allows access to JSON documents through a set of NoSQL-style APIs.

For more information about using JSON in Oracle Database see the Database JSON Developer’s Guide.

12.1. Using the Oracle Database 21c JSON Type in node-oracledb

Oracle Database 21c introduced a dedicated JSON data type with a new binary storage format that improves performance and functionality. To take advantage of the new dedicated JSON type in Oracle Database 21c and later versions, use node-oracledb 5.1 or later. For Thick mode, you must additionally use Oracle Client 21c (or later).

In Oracle Database 21c or later, to create a table with a column called PO_DOCUMENT for JSON data:

CREATE TABLE j_purchaseorder (po_document JSON);

Inserting JSON Data

To insert JavaScript objects directly by binding as oracledb.DB_TYPE_JSON:

const data = { "userId": 1, "userName": "Chris", "location": "Australia" };

await connection.execute(
    `INSERT INTO j_purchaseorder (po_document) VALUES (:bv)`,
    { bv: {val: data, type: oracledb.DB_TYPE_JSON} }
);

Fetching JSON Data

To query a JSON column, use:

const r = await conn.execute(`SELECT po_document FROM j_purchaseorder`);
console.dir(r.rows, { depth: null });

The output is:

[
    {
        PO_DOCUMENT: '{"userId":1,"userName":"Chris","location":"Australia"}'
    }
]

12.1.1. Using Oracle Client Libraries 19c or Earlier

If node-oracledb Thick mode uses Oracle Client Libraries 19c (or earlier), querying an Oracle Database 21c (or later), then JSON column returns a Lob Class BLOB. You can stream the Lob or use lob.getData():

const result = await connection.execute(`SELECT po_document FROM j_purchaseorder`);,

const lob = result.rows[0][0];  // just show first row
const d = await lob.getData();
const j = JSON.parse(d);
console.dir(j,  { depth: null });

The output is:

{ userId: 1, userName: 'Chris', location: 'Australia' }

Note oracledb.fetchAsBuffer will not automatically convert the Oracle Database 21c JSON type to a Buffer. Using it will give ORA-40569: Unimplemented JSON feature. Use await lob.getData() as shown above.

12.2. Using the Oracle Database 12c JSON Type in node-oracledb

In Oracle Database versions 12c or later (prior to Oracle Database 21c), JSON in relational tables is stored as BLOB, CLOB, or VARCHAR2 data. All of these types can be used with node-oracledb in Thin or Thick mode.

The older syntax to create a table with a JSON column is like:

CREATE TABLE j_purchaseorder (po_document BLOB CHECK (po_document IS JSON));

The check constraint with the clause IS JSON ensures only JSON data is stored in that column.

The older syntax can still be used in Oracle Database 21c. However the recommendation is to move to the new JSON type. With the old syntax, the storage can be BLOB, CLOB, or VARCHAR2. Of these, BLOB is preferred to avoid character set conversion overheads.

Inserting JSON Data

When using Oracle Database 12c or later with JSON using BLOB storage, you can insert JSON strings:

const data = { "userId": 1, "userName": "Chris", "location": "Australia" };
const s = JSON.stringify(data);  // change JavaScript value to a JSON string
const b = Buffer.from(s, 'utf8');

const result = await connection.execute(
    `INSERT INTO j_purchaseorder (po_document) VALUES (:bv)`,
    [b]  // bind the JSON string
);

Fetching JSON Data

With Oracle Database 12c (or later), you can fetch VARCHAR2 and LOB columns that contain JSON data in the same way that JSON type columns are fetched when using Oracle Database 21c (or later). This can be done by setting oracledb.future.oldJsonColumnAsObj to the value true as shown below. If you are using node-oracledb Thick mode, you must use Oracle Client 19c (or later) for this setting to work. For example:

oracledb.future.oldJsonColumnAsObj = true;
const r = await conn.execute(`SELECT po_document FROM j_purchaseorder`);
console.dir(r.rows, { depth: null });

12.3. Using BLOB columns with OSON Storage Format in node-oracledb

You can use BLOB columns with Oracle’s optimized binary storage format called OSON if you want the fastest query and update performance. This OSON binary encoding format can be used with BLOB columns in both node-oracledb Thin or Thick modes. For Release 19c, BLOB with format OSON is supported only for Oracle Autonomous Databases. For Thick mode, you must additionally use Oracle Client 21c (or later).

To specify OSON format for BLOB columns, you can use the check constraint with the clause IS JSON FORMAT OSON when creating a table. This check constraint ensures that only binary encoded OSON data is stored in that column. For example, to create a table with a BLOB column containing OSON data:

CREATE TABLE my_table (oson_col BLOB CHECK (oson_col IS JSON FORMAT OSON));

Inserting into BLOB columns with OSON Data

To encode the Javascript value into OSON bytes, you can use the connection.encodeOSON() method. For example:

const data = {key1: "val1"};
// Generate OSON bytes
const osonBytes = connection.encodeOSON(data);
console.log(osonBytes);

This method returns a Buffer and prints an output such as:

<Buffer ff 4a 5a 01 21 02 01 00 05 00 0d 00 00 fa 00 00 04 6b 65 79 32 a4 01 01 00 00 00 07 33 04 76 61 6c 32>

To insert the OSON bytes into the table, you can use:

// Insert the OSON bytes
const result = await connection.execute(
  `INSERT INTO my_table (oson_col) VALUES (:1)`,
  [osonBytes]
);

Fetching BLOB Columns with OSON Data

You can fetch BLOB columns which have the IS JSON FORMAT OSON check constraint enabled in the same way JSON type columns are fetched when using Oracle Database 21c (or later). This can be done by setting oracledb.future.oldJsonColumnAsObj to the value true as shown below. If you are using node-oracledb Thick mode, you must use Oracle Client 21c (or later) for this setting to work. For example:

oracledb.future.oldJsonColumnAsObj = true;
const result = await connection.execute(`SELECT oson_col FROM my_table`);
console.log(result.rows[0][0]);

This prints an output such as:

{key1: "val1"}

If you do not set the oracledb.future.oldJsonColumnAsObj to true, then you can fetch BLOB columns that contain OSON data as shown below:

const result = await connection.execute(
    `SELECT json_object ('hello' value 'world' returning blob format oson
     ) FROM dual`
);
const decodeOsonObj = connection.decodeOSON(result.rows[0][0]);
console.log(decodeOsonObj);

The connection.decodeOSON() decodes the OSON Buffer and returns a Javascript value. This prints an ouput such as:

{ hello: 'world' }

12.4. IN Bind Type Mapping

When binding a JavaScript object as oracledb.DB_TYPE_JSON for oracledb.BIND_IN or oracledb.BIND_INOUT in Oracle Database 21c (or later), JavaScript values are converted to JSON attributes as shown in the following table. The ‘SQL Equivalent’ syntax can be used in SQL INSERT and UPDATE statements if specific attribute types are needed but there is no direct mapping from JavaScript.

JavaScript Type or Value	JSON Attribute Type or Value	SQL Equivalent Example
null	null	NULL
undefined	null	n/a
true	true	n/a
false	false	n/a
Number	NUMBER	json_scalar(1)
String	VARCHAR2	json_scalar('String')
Date	TIMESTAMP	json_scalar(to_timestamp('2020-03-10'), 'YYYY-MM-DD')
Buffer	RAW	json_scalar(utl_raw.cast_to_raw('A raw value'))
Array	Array	json_array(1, 2, 3returning json)
Object	Object	json_object(key 'Fred' value json_scalar(5), key 'George' value json_scalar('A string')returning json)
n/a	CLOB	json_scalar(to_clob('A short CLOB'))
n/a	BLOB	json_scalar(to_blob(utl_raw.cast_to_raw('A short BLOB')))
n/a	DATE	json_scalar(to__date('2020-03-10'), 'YYYY-MM-DD')
n/a	INTERVAL YEAR TO MONTH	json_scalar(to_yminterval('+5-9'))
n/a	INTERVAL DAY TO SECOND	json_scalar(to_dsinterval('P25DT8H25M'))
n/a	BINARY_DOUBLE	json_scalar(to_binary_double(25))
n/a	BINARY_FLOAT	json_scalar(to_binary_float(15.5))

An example of creating a CLOB attribute with key mydocument in a JSON column using SQL is:

const sql = `INSERT INTO mytab (myjsoncol)
             VALUES (JSON_OBJECT(key 'mydocument' value JSON_SCALAR(TO_CLOB(:b)) RETURNING JSON))`;
await connection.execute(sql, ['A short CLOB']);

When mytab is queried in node-oracledb, the CLOB data will be returned as a JavaScript String, as shown by the following table. Output might be like:

{ mydocument: 'A short CLOB' }

12.5. Query and OUT Bind Type Mapping

When getting Oracle Database 21c or later JSON values from the database, the following attribute mapping occurs:

Database JSON Attribute Type or Value	Javascript Type or Value
null	null
false	false
true	true
NUMBER	Number
VARCHAR2	String
RAW	Buffer
CLOB	String
BLOB	Buffer
DATE	Date
TIMESTAMP	Date
INTERVAL YEAR TO MONTH	Not supported. Will give an error.
INTERVAL DAY TO SECOND	Not supported. Will give an error.
BINARY_DOUBLE	Number
BINARY_FLOAT	Number
Arrays	Array
Objects	A plain JavaScript Object

12.6. SQL/JSON Path Expressions

Oracle Database provides SQL access to JSON data using SQL/JSON path expressions. A path expression selects zero or more JSON values that match, or satisfy, it. Path expressions can use wildcards and array ranges. A simple path expression is $.friends which is the value of the JSON field friends.

For example, the previously created j_purchaseorder table with JSON column po_document can be queried like:

SELECT po.po_document.location FROM j_purchaseorder po

With the JSON '{"userId":1,"userName":"Chris","location":"Australia"}' stored in the table, a queried value would be Australia.

The JSON_EXISTS function tests for the existence of a particular value within some JSON data. To look for JSON entries that have a location field:

const result = await connection.execute(
    `SELECT po_document FROM j_purchaseorder WHERE JSON_EXISTS (po_document, '$.location')`
);
const d = result.rows[0][0];      // show only first record in this example
console.dir(d, { depth: null });  // assumes Oracle Database and Client 21c

This query displays:

{ userId: 1, userName: 'Chris', location: 'Australia' }

The SQL/JSON functions JSON_VALUE and JSON_QUERY can also be used.

Note that the default error-handling behavior for these functions is NULL ON ERROR, which means that no value is returned if an error occurs. To ensure that an error is raised, use ERROR ON ERROR.

For more information, see SQL/JSON Path Expressions in the Oracle JSON Developer’s Guide.

12.7. Accessing Relational Data as JSON

In Oracle Database 12.2 or later, the JSON_OBJECT function is a great way to convert relational table data to JSON:

const result = await connection.execute(
    `SELECT JSON_OBJECT ('deptId' IS d.department_id, 'name' IS d.department_name) department
     FROM departments d
     WHERE department_id < :did
     ORDER BY d.department_id`,
    [50]
    );

for (const row of result.rows)
    console.log(row[0]);

This produces:

{"deptId":10,"name":"Administration"}
{"deptId":20,"name":"Marketing"}
{"deptId":30,"name":"Purchasing"}
{"deptId":40,"name":"Human Resources"}

12.8. Portable JSON

Writing applications that can handle all the potential JSON storage types and potential client-server version combinations requires code that checks the Oracle versions and the returned column metadata. This allows the code to do appropropriate streaming or type conversion. It will be simpler to restrict the environment and data types supported by the application. Where possible, migrate to the new JSON type to take advantage of its ease of use and performance benefits.

Here is an example of code that works with multiple versions, with the assumption that older DBs use BLOB storage.

Create a table:

if (connection.oracleServerVersion >= 2100000000) {
    await connection.execute(`CREATE TABLE mytab (mycol JSON)`);
} else if (connection.oracleServerVersion >= 1201000200) {
    await connection.execute(`CREATE TABLE mytab (mycol BLOB CHECK (mycol IS JSON)) LOB (mycol) STORE AS (CACHE)`);
} else {
    throw new Error('This application only works with Oracle Database 12.1.0.2 or greater');
}

Insert data:

const inssql = `INSERT INTO mytab (mycol) VALUES (:bv)`;
const data = { "userId": 2, "userName": "Anna", "location": "New Zealand" };

if (oracledb.oracleClientVersion >= 2100000000 && connection.oracleServerVersion >= 2100000000 ) {
    await connection.execute(inssql, { bv: { val: data, type: oracledb.DB_TYPE_JSON } });
} else {
    const s = JSON.stringify(data);
    const b = Buffer.from(s, 'utf8');
    await connection.execute(inssql, { bv: { val: b } });
}

Query data:

const qrysql = `SELECT mycol
                FROM mytab
                WHERE JSON_EXISTS (mycol, '$.location')
                OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY`;

result = await connection.execute(qrysql, [], { outFormat: oracledb.OUT_FORMAT_ARRAY });
if (result.metaData[0].fetchType == oracledb.DB_TYPE_JSON) {
    j = result.rows[0][0];
} else {
    const d = await result.rows[0][0].getData();
    j = await JSON.parse(d);
}

console.dir(j, { depth: null });