Loading JSON information into each 19c & 23ai tables by way of REST APIs

JSON is the the dominate automobile for permitting for straightforward change of structured info between completely different programming languages and platforms – or for interacting with REST APIs.

Once we cope with the contents of an Oracle Database TABLE by way of REST, we use JSON as the info change format.

I can POST in a JSON object to have it loaded as a row in my desk, and I can GET it again out as one other JSON object. Or if we’re speaking about a number of rows, then we might have an array of mentioned objects.

I’ve proven how this works in another way for GETs in 19 and 23 databases, you’ll be able to learn that publish, right here.

I’m studying right here too, typically

Speaking about REST APIs and JSON and INSERTs, isn’t one thing hew right here. I’ve proven add JSON docs amongst relational information within the request earlier than. However, I made it a lot tougher than it wanted to be, by splitting up the BODY for only a single JSON physique and the relational attributes coming in as REQUEST headers.

That strategy works, however it’s not as clear. And, you probably have MULTIPLE JSON columns to populate, then, it doesn’t work. So, whilst you may suppose I’m an ‘skilled,’ I’m the sort of skilled that also has lots to study and enhance upon.

Studying my weblog means you get to witness my studying path, so preserve that in thoughts if you really feel dangerous or down on your self since you’re simply not getting it.

Instance: 19c Relational Desk, column having IS_JSON constraint

The Code

CREATE TABLE PENDING_CASES 
   (	REQUEST_ID VARCHAR2(256 BYTE) , 
	EMAIL VARCHAR2(256 BYTE) , 
	PRINCIPAL VARCHAR2(256 BYTE) , 
	USAGE_TYPE VARCHAR2(256 BYTE) , 
	TEST_CASE_NAME VARCHAR2(256 BYTE) , 
	RAW_REQUEST VARCHAR2(32000 BYTE) ,  -- this may have JSON information
	PROGRAMMING_LANGUAGE VARCHAR2(256 BYTE) 
   )  DEFAULT COLLATION USING_NLS_COMP ;

CREATE UNIQUE INDEX PENDING_CASES_PK ON PENDING_CASES (REQUEST_ID) 
  ;

ALTER TABLE PENDING_CASES ADD CONSTRAINT ENSURE_JSON CHECK (raw_request is json) ENABLE;

ALTER TABLE PENDING_CASES ADD CONSTRAINT PENDING_CASES_PK PRIMARY KEY (REQUEST_ID)
  USING INDEX  ENABLE;

So let’s take a look at my POST handler for including a row to this desk.

BEGIN
    INSERT INTO PENDING_cases (
        request_id,
        e mail,
        principal,
        usage_type,
        test_case_name,
        raw_request,
        programming_language
    ) VALUES (
        :opc-request-id,
        :e mail,
        :principal,
        :utilization-type,
        :check-case-name,
        JSON_SERIALIZE(:raw-request),
        :programming-language
    );
    COMMIT;
    :status_code := 201;
    :forward_location := :opc-request-id; 
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            :error_message := 'Error: Duplicate main key worth';
            :status_code := 400;
        WHEN OTHERS THEN
            :error_message := 'An error occurred: ' || SQLERRM;
            :status_code := 400;
END;

My request physique will appear to be this –

curl --request POST 
  --url https://the-cloud.someplace.com/ords/admin/requests/pending/ 
  --header 'Content material-Sort: software/json' 
  --header 'Consumer-Agent: insomnia/10.3.0' 
  --data '{
  "opc-request-id": "wow4",
  "e mail": "[email protected]",
  "principal": "some made up stuff",
  "usage-type": "UNITTEST",
  "test-case-name": "bingbong",
  "raw-request": "[{n  "id": 28,n  "Title": "Sweden"n}, {n  "id": 56,n  "Title": "USA"n}, {n  "id": 89,n  "Title": "England"n}]",
  "programming-language": "java"
}'

So I’m going to POST a JSON doc, that has each the relational columns, and in addition the VARCHAR2 which is pseudo-typed as JSON by way of a CHECK constraint.

I must make the JSON content material for the “raw-request’ attribute to be http/url encoded, therefore the escape characters, simple with this on-line instrument.

My INSERT is easy. I exploit an ORDS function that permits to you immediately work with the attributes on the incoming JSON payload by referencing them as :bind variables.

For inserting the JSON content material, I’m going to make use of the JSON_SERIALIZE perform. ORDS treats this information as a VARCHAR because it is available in, because it has no notion of a JSON sort within the 19c database. Why? As a result of it doesn’t exist, but.

The Request

After I do my POST, I can see the response appears to be good, and I can double-confirm this by querying the row out of the desk.

The response is ugly, as a result of I didn’t use my very own 19c {}json trick on the GET handler that handles the 201 location ahead.

This will get even simpler when now we have a local JSON sort that the database and JDBC drivers can navigate.

Instance: 23ai Relational Desk, column having a JSON outlined TYPE

The Code

Our TABLE DDL is easier, no extra CHECK constraint –

CREATE TABLE PENDING_CASES 
   (	REQUEST_ID VARCHAR2(256 BYTE), 
	EMAIL VARCHAR2(256 BYTE), 
	PRINCIPAL VARCHAR2(256 BYTE), 
	USAGE_TYPE VARCHAR2(256 BYTE), 
	TEST_CASE_NAME VARCHAR2(256 BYTE), 
	RAW_REQUEST JSON, 
	PROGRAMMING_LANGUAGE VARCHAR2(256 BYTE)
   ) ;

CREATE UNIQUE INDEX PENDING_CASES_PK ON PENDING_CASES (REQUEST_ID) 
  ;

ALTER TABLE PENDING_CASES ADD CONSTRAINT PENDING_CASES_PK PRIMARY KEY (REQUEST_ID)
  USING INDEX  ENABLE;

And our REST POST HANDLER can be easier, no extra JSON_SERIALIZE perform name over the :column bind for the INSERT.

BEGIN
    INSERT INTO PENDING_cases (
        request_id,
        e mail,
        principal,
        usage_type,
        test_case_name,
        raw_request,
        programming_language
    ) VALUES (
        :opc-request-id,
        :e mail,
        :principal,
        :utilization-type,
        :check-case-name,
        :raw-request,
        :programming-language
    );
    COMMIT;
    :status_code := 201;
    :forward_location := :opc-request-id; 
    EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
            :error_message := 'Error: Duplicate main key worth';
            :status_code := 400;
        WHEN OTHERS THEN
            :error_message := 'An error occurred: ' || SQLERRM;
            :status_code := 400;
END;

The curl is precisely the identical, I solely change up the URL of the request as a result of I’m utilizing my native 23ai FREE virtualbox rig vs my At all times Free 19c Autonomous Database service.

The Request and Response

Since this can be a 23ai database, I don’t have to make use of the aforementioned {}json trick on the GET handler for the 201 location ahead, as ORDS sees the column is typed as JSON from the database.

Abstract

At all times preserve asking questions, at all times be studying, and issues are sometimes simpler than they could seem on the floor.