You have a relational table.
One of those columns just happens to be a BLOB, and has a CHECK constraint that uses IS_JSON.
Sound familiar?
If you’ve missed my posts on SODA for REST, take a moment to reacquaint yourself now.
You can use the Simple Oracle Document Access (SODA) api’s to manage your JSON collections and documents – which behind the scenes are basically what we’ve described in our premise. It’s already built, fully supported, and has the entire lifecycle of a JSON document and collection taken care of.
But.
What if you were going to build your own set of RESTful Web Services that allowed you to create some rows, upload some BLOBs, get those BLOBs back out, change or even remove those BLOBs?
That COULD look like this. And there’s nothing in here that I haven’t shown you before, but here’s a few concepts tied together.
So, we’re going to be able to:
- get a list of all of our departments
- add a new department
- get a single department
- get the JSON column for a department
- change the JSON column for a department
- nuke the JSON column for a department
Our table is ‘the Good Place’ level basic:
CREATE TABLE departments_json ( department_id INTEGER NOT NULL PRIMARY KEY, department_data BLOB NOT NULL ); ALTER TABLE departments_json ADD CONSTRAINT dept_data_json CHECK ( department_data IS json );
In this case, our BLOB (files) is always going to be JSON. I’m using Database 19c, and the most performant way to work with JSON in 19c is by storing it as a BLOB – there’s no characterset conversion overhead as there might be with VARCHAR2s and CLOBs. If I were in 21c, I’d definitely be taking advantage of the database natively provided JSON data type.
JSON is a new SQL and PL/SQL data type for JSON data. Using this type provides a substantial increase in query and update performance. JSON data type uses binary format OSON that is optimized for SQL/JSON query and DML processing. Using the binary format can yield database performance improvements for processing JSON data.
Autonomous Database – Oracle Database 21c Docs
The Code
Here’s the SQL you can just run and build out the API described above for our table.
This looks like a lot of code because of the separate PL/SQL blocks for each handler, but it’s really a trivial amount of code.
-- Generated by ORDS REST Data Services 20.4.1.r0131644 -- Schema: HR Date: Fri Mar 05 02:20:00 2021 -- Will work in any rest enabled schema with the required table present BEGIN ORDS.DEFINE_MODULE( p_module_name => 'table.json', p_base_path => '/json/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => 'work with json docs stored as a column in a table'); ORDS.DEFINE_TEMPLATE( p_module_name => 'table.json', p_pattern => 'departments/:id', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => 'the record'); ORDS.DEFINE_HANDLER( p_module_name => 'table.json', p_pattern => 'departments/:id', p_method => 'GET', p_source_type => 'json/item', p_mimes_allowed => '', p_comments => NULL, p_source => 'select department_id, substr(to_clob(department_data), 0, 45) peek, ''../departments/'' || department_id || ''/doc'' "$the_doc" from departments_json where department_id = :id'); ORDS.DEFINE_TEMPLATE( p_module_name => 'table.json', p_pattern => 'departments/', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => 'all of the records'); ORDS.DEFINE_HANDLER( p_module_name => 'table.json', p_pattern => 'departments/', p_method => 'GET', p_source_type => 'json/collection', p_mimes_allowed => '', p_comments => NULL, p_source => 'select department_id, to_clob(department_data) "{}the_doc", department_id "$self" from departments_json order by department_id asc'); ORDS.DEFINE_HANDLER( p_module_name => 'table.json', p_pattern => 'departments/', p_method => 'POST', p_source_type => 'plsql/block', p_mimes_allowed => '', p_comments => NULL, p_source => 'begin insert into departments_json values (:id, :body); commit; :forward_location := ''./'' || :id; :status := 201; end;'); ORDS.DEFINE_PARAMETER( p_module_name => 'table.json', p_pattern => 'departments/', p_method => 'POST', p_name => 'X-ORDS-FORWARD', p_bind_variable_name => 'forward_location', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'OUT', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'table.json', p_pattern => 'departments/', p_method => 'POST', p_name => 'id', p_bind_variable_name => 'id', p_source_type => 'HEADER', p_param_type => 'INT', p_access_method => 'IN', p_comments => 'user passes new doc id on request header as id'); ORDS.DEFINE_PARAMETER( p_module_name => 'table.json', p_pattern => 'departments/', p_method => 'POST', p_name => 'X-ORDS-STATUS-CODE', p_bind_variable_name => 'status', p_source_type => 'HEADER', p_param_type => 'INT', p_access_method => 'OUT', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'table.json', p_pattern => 'departments/:id/doc', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'table.json', p_pattern => 'departments/:id/doc', p_method => 'GET', p_source_type => 'resource/lob', p_mimes_allowed => '', p_comments => NULL, p_source => 'select ''application/json'', department_data from departments_json where department_id = :id'); ORDS.DEFINE_HANDLER( p_module_name => 'table.json', p_pattern => 'departments/:id/doc', p_method => 'PUT', p_source_type => 'plsql/block', p_mimes_allowed => '', p_comments => NULL, p_source => 'declare how_big integer; begin update departments_json set department_data = :body where department_id = :id; commit; select length(to_clob(department_data)) into how_big from departments_json where department_id = :id; :new_doc := ''Document updated, '' || how_big || '' characters.''; end;'); ORDS.DEFINE_PARAMETER( p_module_name => 'table.json', p_pattern => 'departments/:id/doc', p_method => 'PUT', p_name => 'new_doc', p_bind_variable_name => 'new_doc', p_source_type => 'RESPONSE', p_param_type => 'STRING', p_access_method => 'OUT', p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'table.json', p_pattern => 'departments/:id/doc', p_method => 'DELETE', p_source_type => 'plsql/block', p_mimes_allowed => '', p_comments => 're-initialize the blob, no data', p_source => 'begin update departments_json set department_data = empty_blob () where department_id = :id; commit; :status := 204; end;'); ORDS.DEFINE_PARAMETER( p_module_name => 'table.json', p_pattern => 'departments/:id/doc', p_method => 'DELETE', p_name => 'X-ORDS-STATUS-CODE', p_bind_variable_name => 'status', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'OUT', p_comments => 'need to set response appropriately after nuking the json/blob'); COMMIT; END;
It’s good enough for a prototype. If I had more time, I’d build in exception handling to take care of when you try to add/update a JSON record but the CHECK constraint decides your JSON is crap. I didn’t build a Primary Key on the table, but I don’t need to teach you folks about that, and I just wanted to get straight to the RESTy stuff.
Also, I’m treating the JSON doc in my record as an object itself. This might not fit your concept of a RESTful architectural style – but again, I’m providing this as an example of what you COULD do.
Some Demos
Walking the GETs
Let’s take our first look at the module, then get all of our department records, and then click through to an individual record, and then to JUST the JSON doc.
So in my GET on departments/, I actually show the entire JSON contents for each record, what you show there is completely up to you, it’s your SQL.
Then when I navigate to the individual record, instead of printing the entire record, I just grab the first few characters, and then print a link to the full record.
These links don’t show up ‘magically’ – they show up because I build them into the HANDLER code.
It’s also not an accident that i’m printing the contents of the JSON document and listing it as “the_doc”
This is about as tricky as my API gets. But let’s show one more thing – creating a new record.
Adding a Record (POST)
We’ll look at the PL/SQL block doing the INSERT, then we’ll use a REST client to to the POST. I’m using one called Insomnia.
About production ready code – you’ll see that my INSERT/POST assumes it always works. What happens if you try to send an ID of ‘A’, or if you put something in that doesn’t look like a JSON document? Your API will be as friendly as you want it to be.
Again, the redirect and the 201 response isn’t an accident. Let’s take a look at how that works.
By setting the forward location, I’m telling the the HTTPS client that we want to go to the new record, and that works because I’ve already created a TEMPLATE for /departments/:id and there is a GET handler there to take care of that request.
So in my REST client, I’m doing a POST to /departments, I have the JSON document in the BODY of the request, I have a Request Header called ‘id’ set to 2000, and ORDS takes our request, inserts a new row with 1 and that JSON doc as a BLOB, and then the response is hey, here’s your new record.
Updating via PUT
This is even simpler. We’re not PUTting the entire record up, JUST the JSON document, stored in that BLOB. So on the departments/:id/doc template, we have a PUT handler defined.
I kind of wish I did redirect to the new JSON doc in my table, but I could easily code this by adding a single response HTTP header to tell my browser where to go…
Deleting the JSON Doc (BLOB)
This one is maybe the simplest.
Final Lessons
Don’t spend a lot of time writing code like this if an API is already available. For the use case of keeping a JSON document in a table, that’s there with SODA for REST.
If you’re looking at AUTO REST Enabled Tables or Views, those features don’t account for BLOBs, so you’d have to write SOMETHING like I’ve demonstrated above.
There’s probably better, additional ways these APIs could be implemented on the API side – and I’ve invited the {JSON} team to help me, help you see that.
For example, what if you wanted to do a PARTIAL JSON document update? That’s not hard with built-in SQL support from the database. Stay tuned!
