Microservices and SODA: JSON Data Stored in an Oracle Database Accessed through REST or Java

Simple Oracle Document Access

SODA (Simple Oracle Document Access) is a non-SQL style of storing/retrieving JSON data in an Oracle database.

cold summer alcohol cocktail — Photo by Pixabay on Pexels.com

It’s so easy to work with SODA! Let’s get started.

First, enable ORDS schema in your database.

Second, deploy ORDS in K8s (you can also deploy ORDS standalone in your laptop or in a container if you don’t have one, here you can find an example).

Now let’s try it:

Create a collection:

A collection is a table that transparently stores JSON data for you. Execute the following instruction remarked in bold:

curl -i -X PUT http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON

HTTP/1.1 201 Created

Date: Sat, 22 Dec 2018 19:15:26 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: private,must-revalidate,max-age=0

Location: http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/

Content-Length: 0

Insert JSON in the database:

Download this to a file called po.json and execute the following (please note the ID of the new doc created)

curl -X POST --data-binary @po.json -H "Content-Type: application/json" "http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/"

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z"}],"hasMore":false,"count":1}

Just in case you haven’t noticed the magic of this, take a look at the following picture:

Retrieve document by ID:

The JSON data is stored in a table. Now we can retrieve the document by the ID that was previously given as follows:

curl -X GET http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

{"PONumber": 0,

"Reference": "ABANDA-20140803",

"Requestor": "Amit Banda",

...

"UPCCode": 85391181828},

"Quantity": 9.0}]

Search for documents:

So far, so good. But wait, there is more! Let’s search for the content of a JSON attribute, in this case, documents with PO number is 0:

curl -X POST --data-binary '{"PONumber":"0"}' -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON?action=query

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z","value":{"PONumber": 0,...]}}],"hasMore":false,"count":1}

Bulk insert:

Now, let’s insert a bunch of JSON’s, first download this content and save as POlist.json and execute the following in bold:

curl -X POST --data-binary @POlist.json -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON?action=insert

{"items":[{"id":"0A6DF1E1A3A745D98DC3E3DD648732B2","...22T19:45:42.157916"}],"hasMore":false,"count":70,"itemsInserted":70}

As you can observe, we have inserted several objects with one shot.

Update a document:

Let’s update JSON with PO=0 we searched for before (take note of the ID):

curl -i -X PUT --data-binary '{"PONumber" : "99999"}' -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

HTTP/1.1 200 OK

Date: Sat, 22 Dec 2018 19:55:10 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: no-cache,must-revalidate,no-store,max-age=0

ETag: BC9497081F1E314EE19DD777F2ED7E8425F51C21E7EC1968CEA8110A684C3F05

Last-Modified: Sat, 22 Dec 2018 19:55:10 GMT

Location: http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

Content-Length: 0

List records, delete records and so forth:

And so on… Take a look at the SODA REST, Java and whitepaper documentation.

Discover more features

Take a look at the documentation on how to create more complex queries and many other features.

Last, but not least, notice that the JSON object can be different in each row, there is no constraint that forces to a specific structure.