Use SODA for REST with Autonomous Database
Autonomous Database supports Simple Oracle Document Access (SODA) for REST.
- Overview of Using SODA for REST
SODA for REST is a predeployed REST service that can be used to store JSON documents in your database. - Load Purchase-Order Sample Data Using SODA for REST
Oracle provides a substantial set of JSON purchase-order documents, in plain-text filePOList.json
, as a JSON array of objects, where each such object represents a document. - Use SODA for REST with OAuth Client Credentials
You can access SODA for REST on Autonomous Database using OAuth authentication. Depending on your application, accessing SODA for REST with OAuth authentication can improve performance and security.
Parent topic: Developing RESTful Services in Autonomous Database
Overview of Using SODA for REST
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, 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 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
If you are using Always Free Autonomous Database with Oracle Database 23ai, 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
Parent topic: Use SODA for REST with Autonomous Database
Load Purchase-Order Sample Data Using SODA for REST
Oracle
provides a substantial set of JSON purchase-order documents, in plain-text file
POList.json
, as a JSON array of objects, where each such
object represents a document.
The following examples use the cURL command line tool (http://curl.haxx.se/) to submit REST
requests to the 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 use
SODA for REST with cURL commands from the Oracle Cloud
Shell.
You can load this sample purchase-order data set into a
collection purchaseorder
on your Autonomous Database
with SODA for REST, using these curl commands:
curl -X GET "https://raw.githubusercontent.com/oracle/db-sample-schemas/master/order_entry/POList.json" -o POList.json
curl -X PUT -u 'ADMIN:password' \
"https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/purchaseorder"
curl -X POST -H -u 'ADMIN:password' 'Content-type: application/json' -d @POList.json \
"https://example-db.adb.us-phoenix-1.oraclecloudapps.com/ords/admin/soda/latest/purchaseorder?action=insert"
You can then use this purchase-order data to try out examples in Oracle Database JSON Developer’s Guide.
For example, the following query selects both the id
of
a JSON document and values from the JSON purchase-order collection
stored in column json_document
of table
purchaseorder
. The values selected are from
fields PONumber
, Reference
, and
Requestor
of JSON column
json_document
, which are projected from the
document as virtual columns (see SQL NESTED
Clause Instead of JSON_TABLE for more
information).
SELECT id, t.*
FROM purchaseorder
NESTED json_document COLUMNS(PONumber, Reference, Requestor) t;
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
Parent topic: Use SODA for REST with Autonomous Database
Use SODA for REST with OAuth Client Credentials
You can access SODA for REST on Autonomous Database using OAuth authentication. Depending on your application, accessing SODA for REST with OAuth authentication can improve performance and security.
Perform the following steps to use OAuth authentication to provide limited access to SODA for REST on Autonomous Database:
See Configuring Secure Access to RESTful Services for complete information on secure access to RESTful Services.
Parent topic: Use SODA for REST with Autonomous Database