Overview of Using SODA for REST

SODA for REST is a predeployed REST service for managing JSON documents using CRUD operations (create, read, update and delete), and for querying them using NoSQL-style query-by-example (QBE) requests.

To use SODA for REST you need a database schema (user) that is enabled for Oracle REST Data Services (ORDS). With this SQL code a database user with administrator privileges, such as user ADMIN, can create such an ORDS-enabled schema (in this case TEST). (For information about access using SQL see Oracle Tools for Database Access.)

CREATE USER test IDENTIFIED BY <password>;
GRANT DWROLE TO test;
GRANT UNLIMITED TABLESPACE TO test;
EXEC ords.enable_schema(P_SCHEMA => 'TEST');

SODA for REST is deployed in ORDS under the following URL pattern, where schema corresponds to a REST-enabled database schema.

/ords/schema/soda/latest/*

The following examples use the cURL command line tool (http://curl.haxx.se/) to submit REST requests to the JSON database. However, other 3rd party REST clients and libraries should work as well. The examples use database schema ADMIN, which is REST-enabled. You can SODA for REST with cURL commands from the Oracle Cloud Shell.

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

> curl -X PUT -u 'ADMIN:<password>' \
"https://example-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://example-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://example-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://example-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://example-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
}

This SQL query accesses the fruit collection:

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

The query returns these three rows:

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

Note:

If you are using Always Free Autonomous Database with Oracle Database 21c, Oracle recommends the following:

For projects that were started using a database release prior to Oracle Database 21c, explicitly specify the metadata for the default collection as specified in the example in the section SODA Drivers. For projects started using release Oracle Database 21c or later, just use the default metadata. See SODA Drivers for more information.

These examples show a subset of the SODA and SQL/JSON features. See the following for more information:

• SODA for REST for complete information on Simple Oracle Document Access (SODA)

• SODA for REST HTTP Operations for information on the SODA for REST HTTP operations