3 Using SODA for REST
A step-by-step walkthrough is provided for the basic SODA for REST operations, using examples that you can run. The examples use command-line tool cURL to send REST requests to the server.
The examples assume that you started Oracle REST Data
Services (ORDS) as instructed in Installing SODA for REST,
enabling ORDS in database-schema
.
The examples here generally use http
, not https
.
Which you use depends on how ORDS is configured. For greater security, configure it
to use HTTPS, and then use https
, not http
in your
code, at least for production applications.
Some examples here use the sample JSON documents included in the zip file that you
downloaded in installation step 3. They are in directory
ORDS_HOME/examples/soda/getting-started
.
See Also:
-
http://curl.haxx.se/
for information about command-line tool cURL -
About cURL and Testing RESTful Services in Oracle REST Data Services Developer's Guide for information about cURL and testing RESTful services with ORDS
- Creating a Document Collection with SODA for REST
How to use SODA for REST to create a new document collection is explained. - Discovering Existing Collections with SODA for REST
An example is given of listing the existing collections. - Dropping a Document Collection with SODA for REST
An example is given of dropping a collection. - Inserting a Single Document into a Collection with SODA for REST
An example is given of inserting a document into a collection. - Inserting Multiple Documents into a Collection with SODA for REST
You can bulk-insert a set of documents into a collection using a JSON array of objects. Each object corresponds to the content of one of the inserted documents. - Finding Documents in Collections with SODA for REST
An example is given of retrieving a document from a collection by providing its key. - Replacing Documents in a Collection with SODA for REST
An example is given of replacing a document in a collection with a newer version. For this, you use HTTP operationPUT
. - Removing a Single Document from a Collection with SODA for REST
You can use HTTP operationDELETE
to remove a single document from a collection. - Removing Multiple Documents from a Collection with SODA for REST
You can remove multiple JSON documents from a collection with HTTP operationPOST
, using custom-actiondelete
ortruncate
in the request URL. Usetruncate
to remove all JSON documents from the collection. Usedelete
together with a QBE to delete only the documents that match that filter. - Listing the Documents in a Collection with SODA for REST
An example is given of listing the documents in a collection, using aGET
operation. - Indexing the Documents in a Collection with SODA for REST
You can index the documents in a collection with HTTP operationPOST
, using custom-actionindex
in the request URL. The request body contains an index specification. It can specify B-tree, spatial, full-text, and ad hoc indexing, and it can specify support for a JSON data guide. - Querying Using a Filter Specification with SODA for REST
Examples are given of using a filter specification, or query-by-example (QBE), to define query criteria for selecting documents from a collection. - Patching a Single JSON Document with SODA for REST
You can selectively update (patch) parts of a single JSON document using HTTP operationPATCH
. You specify the update using a JSON Patch specification. - Patching Multiple JSON Documents in a Collection with SODA for REST
You can update (patch) multiple JSON documents in a collection by querying the collection to match those documents and specifying the changes to be made. You specify the update with a JSON Patch specification, using QBE operator$patch
. You use HTTP operationPOST
with custom-actionupdate
in the request URL.
3.1 Creating a Document Collection with SODA for REST
How to use SODA for REST to create a new document collection is explained.
To create a new collection, run this command, where MyCollection
is
the name of the collection. (Replace localhost
with your host name and
8080
with the appropriate port number.)
curl -i -X PUT http://localhost:8080/ords/database-schema/soda/latest/MyCollection
The preceding command sends a PUT
request with URL
http://localhost:8080/ords/
database-schema
/soda/latest/MyCollection
,
to create a collection named MyCollection
. The -i
command-line
option causes cURL to include the HTTP response headers in the output. If the operation succeeds
then the output looks similar to this:
HTTP/1.1 201 Created
Cache-Control: private,must-revalidate,max-age=0
Location: http://localhost:8080/ords/database-schema/soda/latest/MyCollection/
Content-Length: 0
Response code 201 indicates that the operation succeeded. A PUT
operation that results in the creation of a new collection—a PUT collection
operation—returns no response body.
A successful PUT collection
operation creates a database table to store the new collection. One way to see the details of this table is using SQL*Plus command describe
:
SQL> describe "MyCollection"
Name Null? Type
----------------------------------------- -------- -------------
ID NOT NULL VARCHAR2(255)
CREATED_ON NOT NULL TIMESTAMP(6)
LAST_MODIFIED NOT NULL TIMESTAMP(6)
VERSION NOT NULL VARCHAR2(255)
JSON_DOCUMENT BLOB
The preceding table reflects the default collection configuration. The table name was defaulted from the collection name. In this case, the name is mixed-case, so double quotation marks are needed around it. To create a custom collection configuration, provide a collection specification as the body of the PUT
operation.
If a collection with the same name already exists then it is simply opened. If custom metadata is provided and it does not match the metadata of the existing collection then the collection is not opened and an error is raised. (To match, all metadata fields must have the same values.)
Caution:
To drop a collection, proceed as described in Dropping a Document Collection with SODA for REST. Do not use SQL to drop the database table that underlies a collection. Collections have persisted metadata, in addition to the documents that are stored in the collection table.
Related Topics
See Also:
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about the default naming of a collection table
Parent topic: Using SODA for REST
3.2 Discovering Existing Collections with SODA for REST
An example is given of listing the existing collections.
To obtain a list of the collections available in database-schema
, run this command:
curl -X GET http://localhost:8080/ords/database-schema/soda/latest
That sends a GET
request with the URL http://localhost:8080/ords/
database-schema
/soda/latest
and returns this response body:
{ "items" :
[ { "name":"MyCollection",
"properties": { "schemaName":"SCHEMA",
"tableName":"MyCollection",
... }
"links" :
[ { "rel" : "canonical",
"href" :
"http://localhost:8080/ords/database-schema/soda/latest/MyCollection" } ] } ],
"more" : false }
The response body includes all available collections in database-schema
, which in this case is only collection MyCollection
.
A successful GET collection
operation returns response code 200, and the response body is a JSON object that contains an array of available collections and includes the collection specification for each collection.
Parent topic: Using SODA for REST
3.3 Dropping a Document Collection with SODA for REST
An example is given of dropping a collection.
To delete MyCollection
, run this
command:
curl -i -X DELETE http://localhost:8080/ords/database-schema/soda/latest/MyCollection
The preceding command sends a DELETE
request with the URL
http://localhost:8080/ords/
database-schema
/soda/latest/MyCollection
and
returns:
HTTP/1.1 200 OK
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Response code 200 indicates that the operation succeeded. A
DELETE
operation that results in the deletion of a collection—a
DELETE collection
operation—returns no response body.
To verify that the collection was deleted, get the list of available collections in
database-schema
:
curl -X GET http://localhost:8080/ords/database-schema/soda/latest
If MyCollection
was deleted, the preceding command returns
this:
{ "items" : [],
"more" : false }
Create MyCollection
again, so that you can use it in the next
step:
curl -X PUT http://localhost:8080/ords/database-schema/soda/latest/MyCollection
Parent topic: Using SODA for REST
3.4 Inserting a Single Document into a Collection with SODA for REST
An example is given of inserting a document into a collection.
The example uses file po.json
, which was included in the download. The file contains a JSON document that contains a purchase order. To load the JSON document into MyCollection
, run this command:
curl -X POST --data-binary @po.json -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/MyCollection
The preceding command sends a POST
request with the URL http://localhost:8080/ords/
database-schema
/soda/latest/MyCollection
. It outputs something like this:
{ "items" : [
{ "id" : "2FFD968C531C49B9A7EAC4398DFC02EE",
"etag" : "C1354F27A5180FF7B828F01CBBC84022DCF5F7209DBF0E6DFFCC626E3B0400C3",
"lastModified":"2014-09-22T21:25:19.564394Z",
"created":"2014-09-22T21:25:19.564394Z" } ],
"hasMore" : false,
"count" : 1 }
A successful POST object
operation returns response code 200. The response body is a JSON document that contains the identifier that the server assigned to the document when you inserted it into the collection, as well as the current ETag and last-modified time stamp for the inserted document.
Note:
If you intend to retrieve the document then copy the document identifier (the value of field "id"
), to use for retrieval.
3.5 Inserting Multiple Documents into a Collection with SODA for REST
You can bulk-insert a set of documents into a collection using a JSON array of objects. Each object corresponds to the content of one of the inserted documents.
Example 3-1 inserts a JSON array of purchase-order objects into a collection as a set of documents, each object constituting the content of one document. Example 3-2 checks an inserted document.
A successful POST
bulk-insert operation returns response code 200. The response body is a JSON document that contains the identifier, ETag, and last-modified time stamp for each inserted document.
Example 3-1 Bulk-Inserting Documents into a Collection Using a JSON Array of Objects
This example uses file POList.json
, which is included in the download. The file contains a JSON array of purchase-order objects. This command loads the purchase orders into collection MyCollection
as documents.
curl -X POST --data-binary @POList.json -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/custom-actions/insert/MyCollection/
Action insert
causes the array to be inserted as a set of documents, rather than as a single document.
(You can alternatively use the equivalent URL http://localhost:8080/ords/database-schema/soda/latest/MyCollection?action=insert
.)
The command sends a POST
request with the URL http://localhost:8080/ords/
database-schema
/soda/latest/MyCollection
. It outputs something like this:
{
"items" : [
{
"id" : "6DEAF8F011FD43249E5F60A93B850AB9",
"etag" : "49205D7E916EAED914465FCFF029B2795885A1914966E0AE82D4CCDBBE2EAF8E",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created" : "2014-09-22T22:39:15.546435Z"
},
{
"id" : "C9FF7685D48E4E4B8641D8401ED0FB68",
"etag" : "F3EB514BEDE6A6CC337ADA0F5BE6DEFC5D451E68CE645729224BB6707FBE1F4F",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created":"2014-09-22T22:39:15.546435Z"
},
...
],
"hasMore":false,
"count":70
}
Example 3-2 Checking an Inserted Document
You can check an inserted document by copying an id
field value returned by your own POST
bulk-insert operation (not a value from Example 3-1) and querying the collection for a document that has that id
value. Using SQL*Plus or SQL Developer, substitute your copied value for placeholder identifier
here:
SELECT json_value(json_document FORMAT JSON, '$.Reference')
FROM "MyCollection" WHERE id = 'identifier';
JSON_VALUE(JSON_DOCUMENTFORMATJSON,'$.REFERENCE')
-------------------------------------------------
MSULLIVA-20141102
Note:
In the SQL SELECT
statement here, you must specify the table name MyCollection
as a quoted identifier, because it is mixed-case (the table name is the same as the collection name).
Because MyCollection
has the default configuration, which stores the JSON document in a BLOB
column, you must include FORMAT JSON
when using SQL/JSON function json_value
. You cannot use the simplified, dot-notation JSON syntax.
Related Topics
See Also:
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about the default naming of a collection table
Parent topic: Using SODA for REST
3.6 Finding Documents in Collections with SODA for REST
An example is given of retrieving a document from a collection by providing its key.
To retrieve the document that was inserted in Inserting a Single Document into a Collection with SODA for REST, run this command, where id
is the document key that you copied when inserting the document:
curl -X GET http://localhost:8080/ords/database-schema/soda/latest/MyCollection/id
A successful GET document
operation returns response code 200. The response body contains the retrieved document.
If id
does not exist in MyCollection
then the response code is 404, as you can see by changing id
to such an identifier:
curl -X GET http://localhost:8080/ords/database-schema/soda/latest/MyCollection/2FFD968C531C49B9A7EAC4398DFC02EF
{
"type" : "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1",
"status" : 404,
"title" : "Key 2FFD968C531C49B9A7EAC4398DFC02EF not found in collection MyCollection.",
"o:errorCode" : "REST-02001"
}
3.7 Replacing Documents in a Collection with SODA for REST
An example is given of replacing a document in a collection with a newer version. For this, you use HTTP operation PUT
.
The behavior of operation PUT
for a nonexistent document depends on the key-assignment method used by the collection.
-
If the collection uses server-assigned keys (as does collection
MyCollection
) then an error is raised if you try to update a nonexistent document (that is, you specify a key that does not belong to any document in the collection). -
If the collection uses client-assigned keys, then trying to update a nonexistent document inserts into the collection a new document with the specified key.
Retrieve a document from MyCollection
by running this command, where id
is the document identifier that you copied in Listing the Documents in a Collection with SODA for REST:
curl -X GET http://localhost:8080/ords/database-schema/soda/latest/MyCollection/id
The preceding command outputs the retrieved document.
To replace this document with the content of file poUpdated.json
, which was included in the download, execute this command:
curl -i -X PUT --data-binary @poUpdated.json -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/MyCollection/id
The preceding command outputs something like this:
HTTP/1.1 200 OK
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: A0B07E0A6D000358C546DC5D8D5059D9CB548A1A5F6F2CAD66E2180B579CCB6D
Last-Modified: Mon, 22 Sep 2014 16:42:35 PDT
Location: http://localhost:8080/ords/database-schema/soda/latest/MyCollection/023C4A6581D84B71A5C0D5D364CE8484/
Content-Length: 0
The response code 200 indicates that the operation succeeded. A PUT
operation that results in the successful update of a document in a collection — a PUT object
operation — returns no response body.
To verify that the document has been updated, rerun this command:
curl -X GET http://localhost:8080/ords/database-schema/soda/latest/MyCollection/id
The preceding command returns:
{
"PONumber": 1,
"Content" : "This document has been updated...."
}
3.8 Removing a Single Document from a Collection with SODA for REST
You can use HTTP operation DELETE
to remove a single document from a collection.
To remove, from MyCollection
, the document that you retrieved in Finding Documents in Collections with SODA for REST, run this command (where id
is the document identifier):
curl -i -X DELETE http://localhost:8080/ords/database-schema/soda/latest/MyCollection/id
The preceding command sends a DELETE
request with URL http://localhost:8080/ords/
database-schema
/soda/latest/MyCollection/
id
, and it returns this:
HTTP/1.1 200 OK
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0
Response code 200 indicates that the operation succeeded. A DELETE
operation that results in the removal of an object from a collection—a DELETE object
operation—returns no response body.
Parent topic: Using SODA for REST
3.9 Removing Multiple Documents from a Collection with SODA for REST
You can remove multiple JSON documents from a collection with HTTP operation POST
, using custom-action delete
or truncate
in the request URL. Use truncate
to remove all JSON documents from the collection. Use delete
together with a QBE to delete only the documents that match that filter.
Example 3-3 removes the documents where User
field has value TGATES
from collection MyCollection
.Example 3-4 removes all documents from collection MyCollection
.
Example 3-3 Bulk-Removing Matching Documents from a Collection
This example uses the QBE that is in file QBE.1.json to match the nine documents that have "TGATES"
as the value of field User
. It removes (only) those documents from collection MyCollection
.
curl -X POST --data-binary @QBE.1.json -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/custom-actions/delete/MyCollection/
(You can alternatively use the equivalent URL http://localhost:8080/ords/database-schema/soda/latest/MyCollection?action=delete
.)
WARNING:
If you specify delete
as the action, and you use the empty object, {}
, as the filter specification, then the operation deletes all objects from the collection.
Example 3-4 Bulk-Removing All Documents from a Collection
This example removes all documents from collection MyCollection
.
curl -X POST -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/custom-actions/truncate/MyCollection/
(You can alternatively use the equivalent URL http://localhost:8080/ords/database-schema/soda/latest/MyCollection?action=truncate
.)
Parent topic: Using SODA for REST
3.10 Listing the Documents in a Collection with SODA for REST
An example is given of listing the documents in a collection, using a
GET
operation.
You can use parameters to control the result. For example, you can:
-
Limit the number of documents returned
-
Return only document identifiers (keys), only document contents, or both keys and contents
-
Return a range of documents, based on keys or last-modified time stamps
-
Specify the order of the list of returned documents
To list the documents in MyCollection
, returning their
keys and other metadata but not their content, run the following
command.
curl -X GET http://localhost:8080/ords/database-schema/soda/latest/MyCollection?fields=id
The preceding command outputs something like this:
{ "items" :
[ { "id" : "023C4A6581D84B71A5C0D5D364CE8484",
"etag" : "3484DFB604DDA3FBC0C681C37972E7DD8C5F4457ACE32BD16960D4388C5A7C0E",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created" : "2014-09-22T22:39:15.546435Z" },
{ "id" : "06DD0319148E40A7B8AA48E39E739184",
"etag" : "A19A1E9A3A38B1BAE3EE52B93350FBD76309CBFC4072A2BECD95BCA44D4849DD",
"lastModified" : "2014-09-22T22:39:15.546435Z",
"created" : "2014-09-22T22:39:15.546435Z" },
... ],
"hasMore" : false,
"count" : 70,
"offset" : 0,
"limit" : 100,
"totalResults" :70 }
A successful GET collection
operation returns response code
200, and the response body is a JSON document that lists the documents in the collection. If
the collection is empty, the response body is an empty items
array.
To list at most 10 documents in MyCollection
, returning their
keys, content, and other metadata, run this
command:
curl -X GET "http://localhost:8080/ords/database-schema/soda/latest/MyCollection?fields=all&limit=10"
The preceding command outputs something like this:
{ "items" : [ ... ],
"hasMore" : true,
"count" : 10,
"offset" : 0,
"limit" : 10,
"links" :
[ { "rel" : "next",
"href" :
"http://localhost:8080/ords/database-schema/soda/latest/MyCollection?offset=10&limit=10" } ] }
Note:
Including document content makes the response body much larger. Oracle recommends including the content in the response body only if you will need the content later. Retrieving the content from the response body is more efficient that retrieving it from the server.
The metadata in the response body shows that 10 documents were requested
("limit" : 10)
) and 10 documents were returned
("count" : 10)
) , and that more documents are available
("hasMore" : true
). To fetch the next set of documents, you can use the
URL in the field "links"."href"
.
The maximum number of documents returned from a collection by the server is controlled by the following:
-
URL parameter
limit
-
Configuration parameters
soda.maxLimit
andsoda.defaultLimit
Note:
If you intend to
update the document then copy the document identifier (value of field
"id"
), to use for
updating.
Related Topics
See Also:
Understanding Configurable Parameters in Oracle REST Data Services Installation, Configuration, and Development Guide for information about the SODA for REST configuration parameters
Parent topic: Using SODA for REST
3.11 Indexing the Documents in a Collection with SODA for REST
You can index the documents in a collection with HTTP operation POST
, using custom-action index
in the request URL. The request body contains an index specification. It can specify B-tree, spatial, full-text, and ad hoc indexing, and it can specify support for a JSON data guide.
Note:
To create an index with SODA you need Oracle Database Release 12c (12.2.0.1) or later. But to create a B-tree index that for a DATE
or TIMESTAMP
value you need Oracle Database Release 18c (18.1) or later.
A JSON search index is used for full-text search and ad hoc structural queries, and for persistent recording and automatic updating of JSON data-guide information. An Oracle Spatial and Graph index is used for GeoJSON (spatial) data.
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for an overview of using SODA indexing
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about SODA index specifications
-
Oracle Database JSON Developer’s Guide for information about JSON search indexes
-
Oracle Database JSON Developer’s Guide for information about persistent data-guide information as part of a JSON search index
-
Oracle Database JSON Developer’s Guide for information about spatial indexing of GeoJSON data.
Example 3-5 Creating a B-Tree Index for a JSON Field with SODA for REST
This example indexes the documents in collection MyCollection
according to the index specification in file indexSpec1.json
(see Example 3-6).
curl -i -X POST --data-binary @indexSpec1.json -H "Content-Type: application/json" http://localhost:8080/ords/database-schema/soda/latest/custom-actions/index/MyCollection/
This request, using the alternative URI syntax, is equivalent:
curl -i -X POST --data-binary @indexSpec1.json -H "Content-Type: application/json" http://localhost:8080/ords/database-schema/soda/latest/MyCollection?action=index
Example 3-6 B-Tree Index Specification for Field Requestor (file indexSpec1.json)
This example shows the B-tree index specification in file indexSpec1.json
.
The index is named REQUESTOR_IDX
, and it indexes field Requestor
. The data type is not specified, so it is VARCHAR2
, the default. Because field scalarRequired
is specified as true
, if the collection contains a document that lacks the indexed field then an error is raised when the index creation is attempted.
{ "name" : "REQUESTOR_IDX",
"scalarRequired" : true,
"fields" : [{"path" : "Requestor", "order" : "asc"}] }
Parent topic: Using SODA for REST
3.12 Querying Using a Filter Specification with SODA for REST
Examples are given of using a filter specification, or query-by-example (QBE), to define query criteria for selecting documents from a collection.
The examples use the QBE.*.json
files that are included in the zip file that you downloaded in installation step 3. They are in directory ORDS_HOME/examples/soda/getting-started
.
- QBE.1.json
The query-by-example (QBE) in fileQBE.1.json
returns a list of nine documents, each of which has"TGATES"
as the value of fieldUser
. - QBE.2.json
The query-by-example (QBE) in fileQBE.2.json
selects documents where the value of fieldUPCCode
equals"13023015692"
.UPCCode
is a field of objectPart
, which is a field of arrayLineItems
. Because no array offset is specified forLineItems
, the query searches all elements of the array. - QBE.3.json
The query-by-example (QBE) in fileQBE.3.json
selects documents where the value of fieldItemNumber
, in an element of arrayLineItems
, is greater than 4. QBE operator field"$gt"
is required. - QBE.4.json
The query-by-example (QBE) in fileQBE.4.json
selects documents where the value of fieldUPCCode
equals"13023015692"
and the value of fieldItemNumber
equals3
. QBE operator field$and
is optional.
Related Topics
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for an overview of filter specifications and QBE
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for reference information about filter specifications and QBE
Parent topic: Using SODA for REST
3.12.1 QBE.1.json
The query-by-example (QBE) in file QBE.1.json
returns a list of nine documents, each of which has "TGATES"
as the value of field User
.
This is the query in file QBE.1.json
:Foot 1
{ "User" : "TGATES" }
To execute the query, run this command:
curl -X POST --data-binary @QBE.1.json -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/custom-actions/query/MyCollection/
(You can alternatively use the equivalent URL http://localhost:8080/ords/database-schema/soda/latest/MyCollection?action=query
).
A successful POST query
operation returns response code 200 and a list of documents that satisfy the query criteria.
Because the command has no fields
parameter, the default value fields=all
applies, and the response body contains both the metadata and the content of each document.
Note:
Including document content makes the response body much larger. Oracle recommends including the content in the response body only if you need the content for a subsequent operation. Retrieving the content from the response body is more efficient that retrieving it from the server.
To execute the queries in the other QBE.*.json
files, run commands similar to the preceding one.
Parent topic: Querying Using a Filter Specification with SODA for REST
3.12.2 QBE.2.json
The query-by-example (QBE) in file QBE.2.json
selects documents where the value of field UPCCode
equals "13023015692"
. UPCCode
is a field of object Part
, which is a field of array LineItems
. Because no array offset is specified for LineItems
, the query searches all elements of the array.
This is the query in file QBE.2.json
. It has an implied use of operator field "$eq"
.
{ "LineItems.Part.UPCCode" : "13023015692" }
See Also:
Oracle Database Introduction to Simple Oracle Document Access (SODA) for more information
Parent topic: Querying Using a Filter Specification with SODA for REST
3.12.3 QBE.3.json
The query-by-example (QBE) in file QBE.3.json
selects documents where the value of field ItemNumber
, in an element of array LineItems
, is greater than 4. QBE operator field "$gt"
is required.
This is the query in file QBE.3.json
:
{ "LineItems.ItemNumber" : { "$gt" : 4 } }
Parent topic: Querying Using a Filter Specification with SODA for REST
3.12.4 QBE.4.json
The query-by-example (QBE) in file QBE.4.json
selects documents where the value of field UPCCode
equals "13023015692"
and the value of field ItemNumber
equals 3
. QBE operator field $and
is optional.
This is the query in file QBE.4.json
:
{ "$and" : [
{ "LineItems.Part.UPCCode" : "13023015692" },
{ "LineItems.ItemNumber" : 3 } ] }
3.13 Patching a Single JSON Document with SODA for REST
You can selectively update (patch) parts of a single JSON document using HTTP operation PATCH
. You specify the update using a JSON Patch specification.
Note:
To use operation HTTP operation PATCH
you need Oracle Database Release 18c or later.
JSON Patch is a format for specifying a sequence of operations to apply to a JSON document. It is identified by media type application/json-patch+json
, and it is suitable for use with HTTP operation PATCH
.
Use the QBE that is in file QBE.5.json
to retrieve the single document from MyCollection
that has field PONumber
with a value of 1
:
curl -X POST --data-binary @QBE.5.json -H "Content-Type: application/json" http://localhost:8080/ords/database-schema/soda/latest/custom-actions/query/MyCollection/
This is the content of file QBE.5.json
: { "PONumber" : 1 }
.
The preceding command outputs the retrieved document.
To update that document according to the JSON Patch specification in file poPatchSpec.json
(see Example 3-7), execute this command, where key
is the key of the document returned by the preceding command (POST
operation for the QBE in file QBE.5.json
).
curl -i -X PATCH --data-binary @poPatchSpec.json -H "Content-Type: application/json-patch+json"
http://localhost:8080/ords/database-schema/soda/latest/MyCollection/key
If successful, the preceding command returns a 200 HTTP status code.
If unsuccessful, patching is not performed. In particular, if any step (any operation) fails then patching of that document fails. The document is unchanged from its state before attempting the PATCH
HTTP operation.
Example 3-8 shows an example document before successful patching with Example 3-7, and Example 3-9 shows the same document after patching (changes are indicated in bold type).
See Also:
JSON Patch (RFC 6902) for information about the JSON Patch format for describing changes to a JSON document
Example 3-7 JSON Patch Specification (File poPatchSpec.json)
[ { "op" : "test",
"path" : "/ShippingInstructions/Address/street",
"value" : "200 Sporting Green" },
{ "op" : "replace",
"path" : "/ShippingInstructions/Address/street",
"value" : "Winchester House, Heatley Rd" },
{ "op" : "copy",
"from" : "/ShippingInstructions/Phone/0",
"path" : "/ShippingInstructions/Phone/1" },
{ "op" : "replace",
"path" : "/ShippingInstructions/Phone/1/number",
"value" : "861-555-8765" } ]
Example 3-8 JSON Document Before Patching
{ "PONumber" : 1,
"Reference" : "MSULLIVA-20141102",
"Requestor" : "Martha Sullivan",
"User" : "MSULLIVA",
"CostCenter" : "A50",
"ShippingInstructions" : {
"name" : "Martha Sullivan",
"Address" : { "street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America" },
"Phone" : [ { "type" : "Office",
"number" : "979-555-6598" } ] }
... }
Example 3-9 JSON Document After Patching
{ "PONumber" : 1,
"Reference" : "MSULLIVA-20141102",
"Requestor" : "Martha Sullivan",
"User" : "MSULLIVA",
"CostCenter" : "A50",
"ShippingInstructions" : {
"name" : "Martha Sullivan",
"Address" : { "city": "South San Francisco",
"state": "CA",
"zipCode": 99236,
"country": "United States of America",
"street": "Winchester House, Heatley Rd" },
"Phone" : [ { "type" : "Office",
"number" : "979-555-6598" },
{ "type": "Office",
"number": "861-555-8765" } ] }
... }
Related Topics
Parent topic: Using SODA for REST
3.14 Patching Multiple JSON Documents in a Collection with SODA for REST
You can update (patch) multiple JSON documents in a collection by querying the collection to match those documents and specifying the changes to be made. You specify the update with a JSON Patch specification, using QBE operator $patch
. You use HTTP operation POST
with custom-action update
in the request URL.
Note:
To use QBE operator $patch
you need Oracle Database Release 18c or later.
Operator $patch
is specific to SODA for REST; it is not used by other SODA implementations. It is used in a composite filter, at the same level as $query
and $orderby
. (If operators $patch
and $orderby
are both present in a composite filter then $orderby
is ignored.)
The operand of operator $patch
is a JSON Patch specification: a JSON array with object elements that list the patch operations to apply to each document targeted by the query.
JSON Patch is a format for specifying a sequence of operations to apply to a JSON document. It is identified by media type application/json-patch+json
, and it is suitable for use with HTTP operation PATCH
.
If any update step (any operation) specified for patching is unsuccessful for a given document then no patching is performed on that document. Patching continues for other targeted documents, however.
Example 3-10 shows a QBE for patching documents where User
field has value TGATES
. Example 3-11 shows a command that uses that QBE to perform the update operation.
See Also:
-
Oracle Database Introduction to Simple Oracle Document Access (SODA) for information about composite filter specifications
-
JSON Patch (RFC 6902) for information about the JSON Patch format for describing changes to a JSON document
Example 3-10 QBE for Patching Multiple JSON Documents Using QBE Operator $patch
This example shows the QBE that is the content of file qbePatch.json
in the download. The QBE matches the same documents as QBE.1.json. It updates the street address and the first phone number in each document, using the same new values for each document.
Because operator $patch
is used, the query part of the QBE must be specified using operator $query
. The value of operator $patch
is a JSON Patch specification. It replaces street address "200 Sporting Green"
with "176 Gateway Blvd"
and the first number in array Phone
with 999-999-9999
.
{ "$query" : {"User" : "TGATES" },
"$patch" : [ { "op" : "test",
"path" : "/ShippingInstructions/Address/street",
"value" : "200 Sporting Green" },
{ "op" : "replace",
"path" : "/ShippingInstructions/Address/street",
"value" : "176 Gateway Blvd" },
{ "op" : "replace",
"path" : "/ShippingInstructions/Phone/0/number",
"value" : "999-999-9999" } ] }
Example 3-11 Patching Multiple JSON Documents Using HTTP POST with patch Action
This command updates documents according to the QBE of Example 3-10. Each document matching the $query
value is updated.
curl -X POST --data-binary @qbePatch.json -H "Content-Type: application/json"
http://localhost:8080/ords/database-schema/soda/latest/custom-actions/update/MyCollection
Parent topic: Using SODA for REST
Footnote Legend
Footnote 1: An equivalent composite-filter QBE explicitly uses QBE operator$query
: { $query : { "User" : "TGATES" } }
.