Relational to JSON in Oracle Database

More and more often these days, front-end developers want their data in JSON format. And why not? JSON is a simple data-interchange format that’s lightweight and easy to use. Plus, many languages now provide a means of parsing and converting JSON data into native object types. However, not all data is best persisted in JSON format. For many applications, the relational model will be the best way to store data. But can’t we have the best of both worlds? Of course!

There’s no shortage of options when it comes generating JSON from relational data in Oracle Database. In this series, I’ll introduce you to several options, from lower level PL/SQL based solutions to higher level solutions that do more than just generate and parse JSON. Here’s a list of the options I’ll be covering:

• Lower Level
  • 12.2+ SQL
  • PL/JSON
  • APEX_JSON
• Higher Level
  • ORDS
  • Node.js driver for Oracle Database

Which solution is right for you? That depends on your specific use case… Are you simply converting data and using it to populate another table? If so, one of the PL/SQL based options would probably be your best choice. Are you creating an API for others to consume? If so, you’ll probably want to utilize one of the higher level options and only use the PL/SQL options when needed. Are you trying to stream JSON via AJAX in APEX? Then the APEX_JSON package is probably the way to go.

You probably see where I’m going with this. It’s not a one size fits all kind of thing. I’ll provide an overview of how these options can be used to accomplish a goal (defined next). Hopefully, that will help you decide which option would work best for you for a given project.

The Goal

You’re probably familiar with, or at least aware of, the HR schema in Oracle Database, one of several sample schemas that’s included for learning purposes. Well we’re going to convert it to JSON! This is what the HR schema looks like:

You could convert the HR schema into JSON several different ways – it just depends on where you start. For example, if we started with employees table we could traverse down like this:

On the other hand, we could start with the departments table and traverse down like this:

That’s the path we’ll be traversing, from the departments table on down. Given a department id, our goal will be to create the JSON representation of that department. We’ll follow some basic rules to keep the JSON from growing unruly for this demo:

• No more than 4 or 5 attributes per object – This one is pretty self-explanatory. We don’t need to include every column from every table to get the point.
• No more than 3 levels deep – Following one of the attribute chains in the image above, you’ll see “departments > locations > countries > regions”, which is 4 levels deep. Again, we don’t need to traverse down as far as we can to show how traversal works – three levels should do nicely.

In the end, we’ll be creating JSON that looks like this:

There are a couple of things I want to note about this JSON. First of all, it includes all of the possible types of values you’ll find in JSON: objects, arrays, strings, numbers, booleans, and null. To get this output I had to do a little finagling. For null values, I included the commission_pct column of the employees table, as not all employees get a commission. Additionally, I made it a rule that each solution should be written in such a way that if a department doesn’t have a manager or employees, then those properties should be displayed with a null value.

Boolean (true/false) values are not valid data types in Oracle’s SQL engine but I wanted to include them as they are common in JSON. Oracle developers typically use another datatype to represent Boolean values, e.g., 0/1, ‘T’/’F’, or ‘Y’/’N’. But I couldn’t find any of these types of flags in the HR schema so I decided to use another business rule instead: if an employee was hired before January 1st, 2005, then they should have an “isSenior” attribute set to true, otherwise it should be false.

I’ll do a separate blog post on dates in JSON, as they can be tricky. The reason is that dates are not valid data types in JSON. So as with Booleans in Oracle’s SQL engine, developers must make use of other data types (number and string) to represent dates. Issues arise around selecting a date format and handling timezone conversions. To keep things simple in this series, I’ll use a non-standard, string-based date format (DD-MON-YYYY) that is meant for clients to display as a string. Later, when I do the post on dates I’ll include a link here.

Okay, let’s generate some JSON! Here are the links from above for convenience:

• PL/JSON
• APEX_JSON
• ORDS
• Node.js driver for Oracle Database