Using the Database to Manipulate JSON from your POST Body Requests in ORDS

Oracle REST Data Services provides a few features to make working with {JSON} as easy as possible. One of the fundamental ones is making the data from your POST requests readily accessible in your SQL and PL/SQL blocks.

But, as your incoming JSON gets more and more complicated, you might need to step in and write your own SQL to manipulate the document.

Here’s an example from our forums…

The developer wants to take in this {JSON}, and shove it to a table:

{
  "device" : "37AB2C",
  "time" : "1552749097",
  "data" : "0d01da0c1600",
  "signal" : "Average",
  "location" : 
  {
   "lat":49.21706417063655,
   "lng":16.627916371512473,
   "radius":9228,
   "source":2,
   "status":1
  },
  "seqnumber": 1714
}

It’s quite easy to handle this up until we get to the nested document, e.g. ‘location.’

With ORDS, we can automatically reference the json doc values for device, time, data, signal, and seqnumber.

We could simply do an

INSERT INTO tableX VALUES (:device, :TIME, :DATA, :signal, :seqnumber);
commit;

But what about the location data?

We need to read in the POST BODY as a CLOB instead, and then use the database features for working with JSON to build out our INSERT.

Here’s our table:

  CREATE TABLE "FORUMS_NESTED_JSON_TABLE" 
   (	"DEVICE" VARCHAR2(10 BYTE), 
	"TIME" NUMBER(*,0), 
	"DATA" VARCHAR2(20 BYTE), 
	"SIGNAL" VARCHAR2(20 BYTE), 
	"SEQNUMBER" NUMBER(*,0), 
	"LAT" NUMBER(9,6), 
	"LNG" NUMBER(9,6), 
	"RADIUS" NUMBER(*,0), 
	"SOURCE" NUMBER(*,0), 
	"STATUS" NUMBER(*,0), 
	 CONSTRAINT "DEVICE_TIME_XPK" PRIMARY KEY ("DEVICE", "TIME")
  USING INDEX  ENABLE
   ) ;

Remember, starting with version 18.3, we can immediately access the POST body as a CLOB using the automatic bind variable, :body_text.

You can of course work with JSON in the database as a BLOB if you’d like (:body), or if you’re on an older copy of ORDS, just cast the BLOB as a CLOB in your SQL.

But anyway, using the JSON_TABLE function, we can access the data in the nested JSON document using the NESTED PATH syntax on ‘$.location[*].

Here’s my clumsy POST Handler code for doing the INSERT with ORDS:

BEGIN
INSERT INTO forums_nested_json_table
(device, TIME, data, signal, seqnumber, lat, lng, radius, source, status)
SELECT *
  FROM
    json_table(:body_text, '$'
        columns(
            "device" VARCHAR2(10)PATH '$.device',
            "time" NUMBER PATH '$.time',
            "data" VARCHAR2(15)PATH '$.data',
            "signal" VARCHAR2(10)PATH '$.signal',
            "seqnumber" NUMBER PATH '$.seqnumber',
            nested PATH '$.location[*]'
                columns(
                    LAT NUMBER PATH '$.lat',
                    LNG NUMBER PATH '$.lng',
                    RADIUS NUMBER PATH '$.radius',
                    SOURCE NUMBER PATH '$.source',
                    STATUS NUMBER PATH '$.status'
                )
        )
    );
COMMIT;
  :status := 201;
END;

I called my approach clumsy, because I could have also created a nested table or custom complex type to handle the LOCATION data as a single attribute/column of the table, but I went for the quick and dirty approach of one new column per location document attribute – lat, lng, radius, source, and status.

Let’s do the POST and see our new record.

Shout out to Beda – go follow and read his stuff.

Anytime I step into the world of JSON in the database, I ultimately find myself back on Beda’s blog. This one from 2015 gave me exactly what I was looking for.

Beda is our JSON in the Database expert and also frequently hosts Office Hours sessions on his favorite topic.

You can follow Beda on Twitter @bch_t

4 short videos on the JSON Dataguide feature in the Oracle Database: detect, understand and follow schema changes in your JSON data set: https://t.co/NDzF6eVM1M #JSON #Oracle Also do not forget to register for the free JSON Office Hours here: https://t.co/wLJqRPzXq4

— Beda Hammerschmidt (@bch_t) February 8, 2018