DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Keep Calm and Column Wise
  • Amazon Dynamo DB Connector Operations Walkthrough in Mule 4, Part 1
  • Introduction to Couchbase for Oracle Developers and Experts: Part 4: Data Modeling
  • Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects

Trending

  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • How to Practice TDD With Kotlin
  • A Deep Dive Into Firmware Over the Air for IoT Devices
  • How to Configure and Customize the Go SDK for Azure Cosmos DB
  1. DZone
  2. Data Engineering
  3. Databases
  4. So You Want to Use JSON in Oracle Database With Node.js?

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

If you're working with JSON a lot, then storing it in a database is likely something you're faced with. Let's see how to do just that using Node.js with Oracle.

By 
Christopher Jones user avatar
Christopher Jones
·
Updated Aug. 23, 18 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
13.0K Views

Join the DZone community and get the full member experience.

Join For Free

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.

JSON Database Relational database Oracle Database Node.js

Published at DZone with permission of Christopher Jones, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Keep Calm and Column Wise
  • Amazon Dynamo DB Connector Operations Walkthrough in Mule 4, Part 1
  • Introduction to Couchbase for Oracle Developers and Experts: Part 4: Data Modeling
  • Introduction to Couchbase for Oracle Developers and Experts: Part 2 - Database Objects

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!