Relational to JSON in Oracle Database

By July 8, 2015 Uncategorized

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:

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:

HR Schema

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:

HR schema from emps down

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

HR schema from depts down

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:

16 Comments

  • Great post, again πŸ˜‰ Just the level I need to understand everything and being a pleasure to read on! Thanks.

  • Liz says:

    I’d like to see how you created the above json document. Is there a post with the code you used to produce it?

    • danmcghan says:

      Hi Liz,

      There are links in the post that point to 4 different ways you can use to create the document. One may be a better fit than another based on your use case, experience, etc.

      Regards,
      Dan

  • Matt says:

    Yo, where’s the post on dates you promised? Thanks-

  • Gaspar says:

    Dan,
    Superb job!
    This is the BLOG/Article I was looking for and much more!

    Finally someone described the lower to higher levels!

    Thanks
    -Gaspar

  • Alan says:

    Thanks for the post. I was going around googling many sites, including Oracle itself, and found this getting me started with what I wanted for converting Opera PMS data into JSON

  • Venkat says:

    Thank you very much, This blog is superb and what I am looking for I found here. Then I will start working on APEX TO JSON Data conversion.
    Once again good job.
    god, bless you.

  • GEORGE LUDGATE says:

    I am glad you mentioned the non-uniqueness of this approach. There is one tried and true unique approach that works for all schema regardless of how complex the relationships and cardinalities are. First you name all the relationships. You should always do this when data modeling anyway.. Second you make a table for each relationship, even one-to-ones, and move the foreign key/primary key info into these tables from the entities (you can do it as you make the JSON). Third you create the JSON as:

    Entities:
    Departments (list each one, just as done in the example above, but dont bother to put Foreign Key attributes in)
    Employees etc (for each entity table just as in the JSON above but with no nesting)
    … … …
    Relationships:
    works for — (my name for the relationship between Employees & Depts)
    works as — (my name for the Jobs to Empl reln) etc etc etc
    … … …
    The JSON is slightly bigger (in rows) but you can write a script to create this as there is no best hierarchy to “work out”.

Leave a Reply