
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
- Higher Level
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
{ "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, 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:
- Lower Level
- Higher Level
Great post, again π Just the level I need to understand everything and being a pleasure to read on! Thanks.
I’d like to see how you created the above json document. Is there a post with the code you used to produce it?
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
Great article! It inspired me to create a fifth way to create the this JSON document: https://technology.amis.nl/2015/12/22/as_json-relational-to-json-in-oracle-database/
Nice post Anton! Have you considered putting the package up on GitHub? Might get some more eyes on it… Maybe someone will help write some doc too. π
The AMIS blog get enough traffic, average around 5000 hits pro day, and I get enough contributions from people who have changed something in my code, but I had never had anyone volunteering to write documentation. I don’t think GitHub will change that, as PL/JSON still lacks documentation too :). And most of the code I put on the AMIS blog ends up on GitHub, and won’t have anything to do for that. You can find it for instance here: https://github.com/mortenbra/alexandria-plsql-utils or here https://github.com/yallie/as_zip. You can find the as_zip code even in APEX_ZIP π
Fair enough. π
I’m surprised that PL/JSON still lacks documentation. A little over 2 months ago James Sumners, one of the current maintainers, said that they were planning on addressing that in their upcoming 2.0 release. Fingers crossed!
Yo, where’s the post on dates you promised? Thanks-
Hi Matt,
Sorry for the delay. Here’s the first post in a new series:
https://jsao.io/2016/08/working-with-dates-in-javascript-json-and-oracle-database/
Regards,
Dan
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
Thanks Gaspar!
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
Glad it helped, Alan!
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.
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”.
Then I just need a way to query the data with a language like… SQL! π