1. SODA for REST Developer's Guide
  2. Using SODA for REST

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.

Some examples also 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:

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: 

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.

Related Topics

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

Related Topics

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.

Related Topics

Parent topic: Using SODA for REST

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"
}

Related Topics

Parent topic: Using SODA for REST

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...."
}

Related Topics

Parent topic: Using SODA for REST

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.

Related Topics

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 and soda.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:

Oracle REST Data Services Installation, Configuration, and Development Guide for information about configuration parameters soda.maxLimit and soda.defaultLimit

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, or data-guide indexing.

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:

Example 3-5 Indexing 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 file QBE.1.json returns a list of nine documents, each of which has "TGATES" as the value of field User.
  • 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.
  • 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.
  • 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.

Related Topics

See Also:

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 } ] }

See Also:

Oracle Database Introduction to Simple Oracle Document Access (SODA)

Parent topic: Querying Using a Filter Specification with SODA for REST

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:

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

Related Topics

Parent topic: Using SODA for REST


Footnote Legend

Footnote 1: An equivalent composite-filter QBE explicitly uses QBE operator $query: { $query : { "User" : "TGATES" } }.