We publish a number of XML web services from the database using the PL/SQL web toolkit, as described here. In more recent times we’ve had a number of requirements for JSON web services, so we did what most people probably do and Googled for “json pl/sql” and got a link to PL/JSON.
I know about the support for JSON in 12c, but we are not on 12c for these projects and that’s more about consuming JSON, rather than publishing it.
People seemed reasonably happy with PL/JSON, so I thought no more about it. At the weekend, kind-of by accident, I came across the APEX_JSON package that comes as part of APEX 5 and thought, how could I have missed that?
This is not a slight against PL/JSON, but given the choice of using something built and supported by Oracle, that is already in the database (we have APEX 5 in most databases already) or loading something else, I tend to pick the Oracle method. Since then I’ve been having a play with APEX_JSON and I quite like it. Here’s what I wrote while I was playing with it.
If you have done anything with XML in PL/SQL, you should find it pretty simple.
I’m guessing this post will result in a few people saying, “What about ORDS?” Yes I know. Because of history we are still mostly using mod_plsql and OHS, but ORDS is on the horizon. Even so, we will probably continue to use APEX_JSON to do the donkey-work, and just use ORDS to front it.
I spent a bit of time at OpenWorld looking at the JSON support in Oracle Database 12c. I started to write some stuff about it on the plane home and I spent the last two mornings finishing it off. You can see the results here.
I’ve tried to keep it light, since the documentation does a pretty good job at explaining all the variations of the syntax. I’ve also avoided trying to teach people about JSON itself. There is loads of stuff about that on the net already.
For the most part I think the JSON support looks pretty cool. During the process of writing the articles I did notice a few of things that I thought might confuse.
Using dot notation to access JSON in SQL seems like a neat solution, but each reference results in a query transformation that may well leave you with a whole bunch of function calls littered around your SQL. The end result is probably not what you want. I think it is probably better to avoid it and write all the direct function calls yourself, so you know exactly what the optimizer will do.
Typically the query transformations of dot notation result in a JSON_QUERY function call, but the optimizer can substitute a JSON_VALUE call if there is an index that it can take advantage of. That can be a little confusing when you aren’t expecting it. Once again, it might be better to avoid dot notation so as not to confuse.
If you are careful, the indexing of JSON data is pretty straight forward, but if you aren’t aware of how the query transformations work or you forget how very small changes in function parameters affect index usage, you can chase your tail trying to figure out why you aren’t able to use your indexes.
Until the REST APIs are released, the only way you can use this stuff is from the server side, so it’s not really something you can hand out to developers who are expecting to use just another document store. I had a play with the REST stuff during a hands-on lab at OpenWorld and it looked kind-of cool. When it’s released I’ll write an article about it and run it by some of the folks at work to see how they think it compares to other document databases…