A blog about JSON in the Oracle database Technology

  • June 5, 2015

Using REST to access JSON data in the Oracle Database (SODA for REST)

Beda Hammerschmidt

SODA for REST provides features similar to SODA for Java (covered in a previous posting: https://blogs.oracle.com/jsondb/entry/oracle_rdbms_as_a_nosql): 

  • JSON data (documents) are stored in collections (each collection backed by an automatically created and maintained Oracle Database table)
  • documents are identified by IDs
  • on document insert and replace, additional metadata is created or updated (ID, creation timestamp, last modified timestamp, eTag)
  • queries can be expressed without SQL. Instead queries are expressed as ‘Query by Example (QBE)’ documents in JSON format

The goal of the SODA API (for Java and REST) is to make developing applications faster and easier while still enjoying all the benefits of the Oracle Database.  

SODA for REST is usable from any programming language, as long as it allows HTTP requests to be made (e.g. JavaScript, Python, etc.).

In this first posting we will use the Chrome extension ‘Postman’ to make calls to the database. You could also use ‘c url’.

SODA for REST requires Oracle Database patch 20885778 to be installed. Obtain the patch from My Oracle Support
(https://support.oracle.com). Select tab Patches &
Updates. Search for the patch number, 20885778 or access it directly at
this URL: https://support.oracle.com/rs?type=patch&id=20885778.

SODA for REST ships with ORDS 3.0 (Oracle Rest Data Services): http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html

First we need to install ORDS. Download the ORDS file and unzip it into a new folder. In that folder you should see a file called ‘ords.war’:

We will install ORDS using SQLDeveloper. If you do not have SQL Developer, then please download it from here: http://www.oracle.com/technetwork/developer-tools/sql-developer .

You can also install ORDS outside of SQLDeveloper (without a GUI), see here for instructions: http://docs.oracle.com/cd/E56351_01/doc.30/e58123/rest.htm#ADRST107 .

In SQLDeveloper, in the menu ‘Tools’, you will find the option ‘REST Data Services’. Click Install:

Point the installer to the ‘ords.war’ file that you just
downloaded and unzipped. Point the configuration directory to the folder that
contains ‘ords.war’.

Provide the connection information to the database. Provide
a password for the ORDS_PUBLIC_USER schema (e.g. ORDS):

Do NOT install the PL/SQL gateway.

Also, APEX REST Services are not needed (we want to use ORDS
REST services instead).

Define the port where the REST service is listening:

No need to create database users for an ORDS administrator
or RESTful services user.

Finish the installation. (The installation process may request you to
authenticate as a user with the SYSDBA role in order to install the ORDS

Click ‘Finish’ and you should see the ORDS
server outputting log information into the window at the bottom when it starts:

Now, ORDS is installed (on the localhost), and the server is listening (on the port you configured, 8080 in the above example) for requests.

Before we can use the SODA for REST service, we need to (create and) activate a database schema for it. 

You can pick an existing schema (e.g. SCOTT) or create a new one.  Connect to the schema and then execute a PL/SQL command to enable it for use with ORDS:

conn scott/tiger@localhost:1521/pdborcl

SQL> exec ords.enable_schema

PL/SQL procedure successfully completed.

Also, we disable security (authentication, etc) in order to show simple REST requests without adding security related complexity.

Note: disabling security in a production environment is not recommended!

SQL> begin

PL/SQL procedure successfully completed.

You can now test the installation of ORDS by making a request to the server using your Postman (or another tool that can make HTTP calls). 

The url is http://localhost:8080/ords/scott/soda/latest/
If you have used a different schema than ‘scott’ then replace it with your schema name (in lower case).  
If you see JSON being returned in response to the request than the installation was successful.

In case you wonder what this JSON output tells us: it’s a list of all collections that exists in that schema. Since we do not have any collections yet, the array ‘items’ is empty. Now, let’s create a new collection, called ‘customers’. We do this with a PUT request, here performed using Postman.

After this, the same URL we invoked before (to list all collections), will show our newly added collection called customers:

What’s returned here is collection metadata for the newly created collection. As you can see from this particular metadata, the collection maps to a table with the name “customers”, and JSON data is stored in a BLOB column. Columns are included to store an auto-generated version, last-modified timestamp, and creation-time. Collection metadata is highly configurable, though that’s a topic for another posting. Here, the default collection metadata settings are used.

Adding a document is a POST operation to the very same url that we used to create the collection.

This time we get a response which tells us the ID that was used to store the new document. We also get ‘eTag’, which can be used for caching to identify changed documents, as well as the last-modified and creation timestamps.

Finding a document by ID is a GET request with
the ID
following the collection name:

Deleting a document is a
simple DELETE request to the same URL http://localhost:8080/ords/scott/soda/latest/customers/C4C0208AAA1842F588C781A02484DBA7 (note: the trailing part of the URL starting with "C4C0..." will be different in your case, since the auto-generated ID, based on a UUID, will be different).

Now, how can we retrieve one (or more) documents based on some properties (e.g. all customers having the same zip code)?

We use a QBE (Query By Example), which is itself a JSON document that defines what we’re interested in. Given the JSON customer data we’ve been using in this posting, to find all JSONs representing customers with the same zip code, the QBE is:

{"address.zip": 94065}

A POST request to collection url with the parameter ‘action=query’ and a QBE as the body allows us to query all documents in the collection:

The result is an array of the items (circled in red) that match the supplied QBE. In this case, the array contains only one item (circled in green), since the QBE matches the only item we have in the collection. The result also contains information (circled in blue) about the number of items in the array and if there are more (which allows for result set pagination, though that’s a topic for another blog posting). More complex QBEs can be expressed by adding constructs like ‘$and’, ‘$or’,’$in’, etc to the JSON representing the QBE.

What are some other interesting QBEs we can run, assuming more documents with similar structure in the collection?

To find customers in a list of different zip codes the QBE is: 

    "address.zip": {"$in":[94065, 94070]}

A range query can be expressed with:

    "address.zip": {"$gt":94000,  "$lt":95000}

A query on multiple properties can be expressed with Boolean AND, OR:

    "$or": [
            "first": "Beda"
            "address.zip": 94000

As you can see predicates can be nested arbitrarily using logical expressions.

In case no document matches the QBE you will get this response to your query:

Since the data is stored in a regular Oracle Database table (automatically managed by SODA for REST) one can easily use SQL to query it directly. For instance, you can run reports with SQL (using JSON_TABLE), or perform joins with existing relational tables. The document/collection API just provides a level of abstraction over Oracle Database tables to enable the ease of use of a NoSQL document store. You can still drop down into SQL if needed.

If you want to read more:

SODA for REST documentation: https://docs.oracle.com/cd/E56351_01/doc.30/e58123/toc.htm

SODA for JAVA documentation: http://docs.oracle.com/cd/E63251_01/doc.12/e58124/soda.htm

Oracle REST data services (ORDS): http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.