So You Want to Use JSON in Oracle Database With Node.js?

The JavaScript JSON.parse() and JSON.stringify() methods make it easy to work with JavaScript objects in Node.js and store them in Oracle Database using the node-oracledb module.

I'll start with some examples showing a simple, naive, implementation which you can use with all versions of Oracle Database. Then I'll go on to show some of the great JSON functionality introduced in Oracle Database 12.1.0.2.

The examples below use the async/await syntax available in Node 7.6, but they can be rewritten to use promises or callbacks if you have an older version of Node.js.

Storing JSON as Character Data in Oracle Database 11.2

At the simplest, you can stores JSON as character strings, such as in column C of MYTAB:

CREATE TABLE mytab (k NUMBER, c CLOB);

Using a CLOB means we don't need to worry about the length restrictions of a VARCHAR2.

A JavaScript object like myContent can easily be inserted into Oracle Database with the node-oracledb module by stringifying it:

const oracledb = require('oracledb');

let connection, myContent, json, result;

async function run() {

  try {
    connection = await oracledb.getConnection(
      {user: "hr", password: "welcome", connectString: "localhost/orclpdb"});

    myContent = {name: "Sally", address: {city: "Melbourne"}};
    json = JSON.stringify(myContent);
    result = await connection.execute(
      'insert into mytab (k, c) values (:kbv, :cbv)',
      { kbv: 1, cbv: json } ); 
    console.log('Rows inserted: ' + result.rowsAffected);

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
await connection.close();
      } catch (err) {
console.error(err);
      }
    }
  }
}

run();

If you are just inserting one record you may want to auto-commit, but make sure you don't unnecessarily commit, or break transactional consistency by committing a partial set of data:

myContent = {name: "Sally", address: {city: "Melbourne"}};
json = JSON.stringify(myContent);
result = await connection.execute(
  'insert into mytab (k, c) values (:kbv, :cbv)',
  { kbv: 1, cbv: json },
  { autoCommit: true} ); 
console.log('Rows inserted: ' + result.rowsAffected);

The output is:

Rows inserted: 1

To retrieve the JSON content you have to use a SQL query. This is fine when you only need to look up records by their keys:

result = await connection.execute(
  'select c from mytab where k = :kbv',
  { kbv: 1 },  // the key to find
  { fetchInfo: {"C": {type: oracledb.STRING } }});
if (result.rows.length) {
  js = JSON.parse(result.rows[0]);
  console.log('Name is: ' + js.name);
  console.log('City is: ' + js.address.city);
} else {
  console.log('No rows fetched');
}

The fetchInfo clause is used to return the CLOB as a string. This is simpler and generally faster than the default, streamed access method for LOBs. (Streaming is great for huge data streams such as videos.)

The JSON.parse() call converts the JSON string into a JavaScript object so fields can be accessed like "js.address.city".

Output is:

Name is: Sally
City is: Melbourne

Code gets trickier if you need to match JSON keys in the query. You need to write your own matching functionality using LOB methods like dbms_lob.instr():

result = await connection.execute(
                 'select c from mytab
                  where dbms_lob.instr(c, \'"name":"\' || :cbv ||\'"\') > 0',
  { cbv: 'Sally' },
  { fetchInfo: {"C": {type: oracledb.STRING } }});
if (result.rows.length) {
  js = JSON.parse(result.rows[0]);
  console.log('Name is: ' + js.name);
  console.log('City is: ' + js.address.city);
} else {
  console.log('No rows fetched');
}

You can see this could be slow to execute, error-prone to do correctly, and very hard to work with when the JSON is highly nested. But there is a solution...

Oracle Database 12c JSON

With Oracle 12.1.0.2 onward, you can take advantage of Oracle's JSON functionality. Data is stored as VARCHAR2 or LOB so the node-oracledb code is similar to the naive storage solution above. However, in the database, extensive JSON functionality provides tools for data validation, indexing and matching, for working with GeoJSON, and even for working with relational data. Check the JSON Developer's Guide for more information. You may also be interested in some of the JSON team's articles.

To start with, when you create a table, you can specify that a column should be validated so it can contain only JSON:

c CLOB CHECK (c IS JSON)) LOB (c) STORE AS (CACHE)

In this example, I also take advantage of Oracle 12c's "autoincrement" feature called "identity columns." This automatically creates a monotonically increasing sequence number for the key. The complete CREATE TABLE statement used for the following examples is:

CREATE TABLE myjsontab (k NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY(START WITH 1),
                        c CLOB CHECK (c IS JSON)) LOB (c) STORE AS (CACHE);

Strictly speaking, since I know my application will insert valid JSON, I could have improved database performance by creating the table without the CHECK (c IS JSON) clause. However, if you don't know where your data is coming from, letting the database do validation is wise.

Inserting a JavaScript object data uses the same stringification as the previous section. Since we don't need to supply a key now, we can use a DML RETURNING clause to get the new key's autoincremented value:

myContent = {name: "Sally", address: {city: "Melbourne"}};
json = JSON.stringify(myContent);
result = await connection.execute(
  'insert into myjsontab (c) values (:cbv) returning k into :kbv',
  { cbv: json,
    kbv: { type: oracledb.NUMBER, dir: oracledb.BIND_OUT } },
  { autoCommit: true} ); 
console.log('Data key is: ' + result.outBinds.kbv);

This inserts the data and returns the key of the new record. The output is:

Data key is: 1

To extract data by the key, a standard SQL query can be used, identical to the naive CLOB implementation previously shown.

Oracle Database's JSON functionality really comes into play when you need to match attributes of the JSON string. You may even decide not to have a key column. Using Oracle 12.2's 'dotted' query notation you can do things like:

result = await connection.execute(
  'select c from myjsontab t where t.c.name = :cbv',
  { cbv: 'Sally' },
  { fetchInfo: {"C": {type: oracledb.STRING } }});
if (result.rows.length) {
  js = JSON.parse(result.rows[0]);
  console.log('Name is: ' + js.name);
  console.log('City is: ' + js.address.city);
} else {
  console.log('No rows fetched');
}

Output is:

Name is: Sally
City is: Melbourne

(If you use Oracle Database 12.1.0.2, then the dotted notation used in the example needs to be replaced with a path expression, see the JSON manual for the syntax).

Other JSON functionality is usable, for example, to find any records that have an "address.city" field:

select c FROM myjsontab where json_exists(c, '$.address.city')

If you have relational tables, Oracle Database 12.2 has a JSON_OBJECT function that is a great way to convert relational table data to JSON:

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`,
  { did: 50 },
  { fetchInfo: {"C": {type: oracledb.STRING } }});
if (result.rows.length) {
  for (var i = 0; i < result.rows.length; i++) {
    console.log("Department: " + result.rows[i][0]);
    js = JSON.parse(result.rows[i][0]);
    console.log('Department Name is: ' + js.name);
  }
} else {
  console.log('No rows fetched');
}

Output is:

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

If you are working with JSON tables that use BLOB storage instead of CLOB, for example:

CREATE TABLE myjsonblobtab (k NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY(START WITH 1),
                            c BLOB CHECK (c IS JSON)) LOB (c) STORE AS (CACHE);

Then you need to bind a Buffer for insert:

myContent = {name: "Sally", address: {city: "Melbourne"}};
json = JSON.stringify(myContent);
b = Buffer.from(json, 'utf8');
result = await connection.execute(
  'insert into myjsonblobtab (k, c) values (:kbv, :cbv)',
  { kbv: 1,
    cbv: b },
  { autoCommit: true} ); 
console.log('Rows inserted: ' + result.rowsAffected);

Querying needs to return a Buffer too:

result = await connection.execute(
  'select c from myjsonblobtab t where t.c.name = :cbv',
  { cbv: 'Sally' },
  { fetchInfo: {"C": {type: oracledb.BUFFER } }});
if (result.rows.length) {
  js = JSON.parse(result.rows[0].toString('utf8'));
  console.log('Name is: ' + js.name);
  console.log('City is: ' + js.address.city);
} else {
  console.log('No rows fetched');
}

A Final JSON Tip

One final tip is to avoid JSON.parse() if you don't need it. An example is where you need to pass a JSON string to a web service or browser. You may be able to pass the JSON string returned from a query directly. In some cases, the JSON string may need its own key, in which case simple string concatenation may be effective. In this example, the Oracle Locator method returns a GeoJSON string:

result = await connection.execute(
  `select sdo_util.to_geojson(
 sdo_geometry(2001, 8307,
  sdo_point_type(-90, 20, null),
  null, null)) as c from dual`,
  { }, // no binds
  { fetchInfo: {"C": {type: oracledb.STRING } }});
json = '{"geometry":' + result.rows[0][0] + '}';
console.log(json);

The concatenation above avoids the overhead of a parse and re-stringification:

js = JSON.parse(result.rows[0][0]);
jstmp = {geometry: js};
json = JSON.stringify(jstmp);

Summary

The JavaScript JSON.parse() and JSON.stringify() methods make it easy to work with JSON in Node.js and Oracle Database. Combined with node-oracledb's ability to work with LOBs as Node.js Strings, database access is very efficient. Oracle Database 12.1.0.2's JSON features make JSON operations in the database simple. Advances in Oracle Database 12.2 and 18c further improve the functionality and usability.

Resources

Node-oracledb installation instructions are here.

Node-oracledb documentation is here.

Issues and questions about node-oracledb can be posted on GitHub.

The Oracle JSON Developer's Guide is here.