Develop SODA for REST with Autonomous Database

Autonomous Data Warehouse supports Simple Oracle Document Access (SODA) for REST.

Simple Oracle Document Access (SODA) for REST is a pre-deployed REST service that can be used to store JSON documents in an Autonomous Data Warehouse database. SODA enables flexible, NoSQL-style application development without having to use SQL. With SODA, JSON documents are stored in named collections and managed using simple CRUD operations (create, read, update and delete). And while SQL isn't required, JSON stored in SODA collections is still fully accessible from SQL when needed. For example, an operational application may be fully built using SODA (without SQL) but then the data may be later analyzed using SQL from outside of the application. Autonomous Database SODA gives application developers the best of the NoSQL and SQL worlds - fast, flexible, and scalable application development without losing the ability to leverage SQL for analytics and reporting.

SODA for REST is deployed in ORDS under the following URL pattern:

/ords/schema/soda/latest/*

Where schema corresponds to the REST enabled database schema (for example, "admin").

The following examples use the cURL command line tool (http://curl.haxx.se/) to submit REST requests to the Autonomous Data Warehouse database. However, other 3rd party REST clients and libraries should work as well.

This command creates a new collection named "fruit" in the ADMIN schema:

> curl -X PUT -u 'ADMIN:password' \
"https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

These commands insert three JSON documents into the fruit collection:

> curl -X POST -u 'ADMIN:password' \
 -H "Content-Type: application/json" --data '{"name":"orange", "count":42}' \
 "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

{"items":[{"id":"6F7E5C60197E4C8A83AC7D7654F2E375"...
 
> curl -X POST -u 'ADMIN:password' \
 -H "Content-Type: application/json" --data '{"name":"pear", "count":5}' \
 "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

{"items":[{"id":"83714B1E2BBA41F7BA4FA93B109E1E85"...
 
> curl -X POST -u 'ADMIN:password' \
 -H "Content-Type: application/json" --data '{"name":"apple", "count":12, "color":"red"}' \
 "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit"

{"items":[{"id":"BAD7EFA9A2AB49359B8F5251F0B28549"...

This example retrieves a stored JSON document from the collection:

> curl -X POST -u 'ADMIN:password' \
 -H "Content-Type: application/json" --data '{"name":"orange"}' \
 "https://rzsf8o3up2w8rzc-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/fruit?action=query"

{
  "items": [
    {
      "id":"6F7E5C60197E4C8A83AC7D7654F2E375",
      "etag":"57215643953D7C858A7CB28E14BB48549178BE307D1247860AFAB2A958400E16",
      "lastModified":"2019-07-12T19:00:28.199666Z",
      "created":"2019-07-12T19:00:28.199666Z",
      "value":{"name":"orange", "count":42}
    }
  ],
  "hasMore":false,
  "count":1
}

And finally, the following sample SQL query accesses the fruit collection:

SELECT 
     f.json_document.name,
     f.json_document.count,
     f.json_document.color
FROM fruit f;

This query returns three rows:

name      count     color
--------- --------- -------
orange    42        null
pear      5         null
apple     12        red

These examples show a small subset of the SODA and SQL/JSON features. For more information see:

See SODA for REST for information on Simple Oracle Document Access (SODA).

See SODA for REST HTTP Operations for information on the SODA for REST HTTP operations.