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.

OpenAPI provided by {json} spit out by ORDS for our module.

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.

Just click the module card’s hamburger button ti get your json api doc or the DDL scripts.

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.

I like that I can test/see the SQL run in the GET handler editor.

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”

“$alias” – tells ORDS to gen a LINK, “{}json” – tells ords this is JSON content, don’t format it

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.

After the record is inserted – I’m taking you to the record, and sending back a 201.

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.

I’m setting the response status code to 201, and I’m redirecting the client to the new resource.

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.

Instead of redirecting to the record, I’m sending back a simple message with the character count.

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…

Instead of taking you to the updated doc, I tell you yeah that worked.

Deleting the JSON Doc (BLOB)

This one is maybe the simplest.

My response body is NULL, so the proper status code is 204 vs 200.

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!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment