node-oracledb

12. Oracle Database 12.1 JSON Datatype

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.

JSON data in the database is stored as BLOB, CLOB or VARCHAR2 data.
This means that node-oracledb can easily insert and query it.

As an example, the following table has a PO_DOCUMENT column that is
enforced to be JSON:

CREATE TABLE po (po_document VARCHAR2(4000) CHECK (po_document IS JSON));

To insert data using node-oracledb:

var data = { customerId: 100, item: 1234, quantity: 2 };
var s = JSON.stringify(data);  // change JavaScript value to a JSON string
connection.execute(
  "INSERT INTO po (po_document) VALUES (:bv)",
  [s]  // bind the JSON string
  function (err) {
  . . .
  });

Queries can access JSON with Oracle JSON path expressions. These
expressions are matched by Oracle SQL functions and conditions to
select portions of the JSON data. Path expressions can use wildcards
and array ranges. An example is $.friends which is the value of
JSON field friends.

Oracle provides SQL functions and conditions to create, query, and
operate on JSON data stored in the database. An example is the Oracle
SQL Function JSON_TABLE which projects JSON data to a relational
format effectively making it usable like an inline relational view.
Another example is JSON_EXISTS which tests for the existence of a
particular value within some JSON data:

This example looks for JSON entries that have a quantity field:

conn.execute(
  "SELECT po_document FROM po WHERE JSON_EXISTS (po_document, '$.quantity')",
  function(err, result)
  {
    if (err) {
      . . .
    } else {
      var js = JSON.parse(result.rows[0][0]);  // show only first record in this example
      console.log('Query results: ', js);
    }
  });

After the previous INSERT example, this query would display:

{ customerId: 100, item: 1234, quantity: 2 }

See selectjson.js
and selectjsonclob.js
for runnable examples.

For more information about using JSON in Oracle Database see JSON in
Oracle Database.