Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Relational to JSON in Oracle Database

DZone's Guide to

Relational to JSON in Oracle Database

More and more often, front-end devs want their data in JSON format. But for many apps, the relational model is the best way to store data. Can we have the best of both worlds? Of course!

· Database Zone
Free Resource

Find out how Database DevOps helps your team deliver value quicker while keeping your data safe and your organization compliant. Align DevOps for your applications with DevOps for your SQL Server databases to discover the advantages of true Database DevOps, brought to you in partnership with Redgate

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 are 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 the 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 four or five 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 three levels deep. Following one of the attribute chains in the image above, you'll see "departments > locations > countries > regions", which is four 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:

{
  "id": 10,
  "name": "Administration",
  "location": {
    "id": 1700,
    "streetAddress": "2004 Charade Rd",
    "postalCode": "98199",
    "country": {
      "id": "US",
      "name": "United States of America",
      "regionId": 2
    }
  },
  "manager": {
    "id": 200,
    "name": "Jennifer Whalen",
    "salary": 4400,
    "job": {
      "id": "AD_ASST",
      "title": "Administration Assistant",
      "minSalary": 3000,
      "maxSalary": 6000
    }
  },
  "employees": [
    {
      "id": 200,
      "name": "Jennifer Whalen",
      "isSenior": true,
      "commissionPct": null,
      "jobHistory": [
        {
          "id": "AD_ASST",
          "departmentId": 90,
          "startDate": "17-SEP-1995",
          "endDate": "17-JUN-2001"
        },
        {
          "id": "AC_ACCOUNT",
          "departmentId": 90,
          "startDate": "01-JUL-2002",
          "endDate": "31-DEC-2006"
        }
      ]
    }
  ]
}

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, i.e. 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 1, 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:

Align DevOps for your applications with DevOps for your SQL Server databases to increase speed of delivery and keep data safe. Discover true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,relational database ,json ,oracle ,tutorial

Published at DZone with permission of Dan McGhan, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}