Hierarchical JSON with Stew Ashton & ORDS REST APIs

One of the core features of ORDS is generating JSON responses based on your SQL query results or your PL/SQL stored procedure output.

But, what if YOU are generating the JSON?

We’ve talked about this technique before, but I saw an interesting post from friend Stew Ashton today on some PL/SQL he put together for dynamically generating hierarchical JSON.

And I thought, I should give that a try in ORDS and then share it with you, so here is that post.

Go read Stew’s post now

Since you read his blog post, why don’t you go ahead and follow him on Twitter as well!

Too late for @BrynLite, a use case where PL/SQL is faster than SQL… https://t.co/ZlUCKzJjiF
— Stew Ashton (@StewAshton) February 8, 2022

I’ve borrowed his package and compiled it to my 19c database.

Amazing what can be done with just a few lines of PL/SQL, right?

And now with that package available to me, I took his example/use case and baked that into an ORDS REST API via a GET Handler.

A few tricks or concepts at play here

First things first, the Source Type of my REST API is ‘resource/lob’ or as it’s sometimes shown, ‘Media.’ This tells ORDS just to ‘stream’ the content directly back to the client (browser in this case), along with the application mime type.

In the SELECT query, the first column is the mime type – this tells Chrome what it’s got coming in the response payload, and the 2nd column is a LOB (character or binary, your choice).

The main reason I wanted to showcase Stew’s code today, other than he is awesome and I like Stew, is that I frequently get asked how to have ORDS generate mixed-case or case-sensitive JSON attributes. And, the way to do that, is to generate the JSON response yourself.

So in the code block, you can see I’m specifying the attribute labels using InitCap.

And finally a tried and true technique, you can pass values off of the request URI directly to your handler block of code using a :bind variable notation.

So when I do a: