In my first article on SODA (Simple Oracle Document Access) and PL/SQL, I showed you how to perform SODA operations in Oracle Database 18c and higher to create SODA collections and documents in your schema.
In this article, I explore how to use methods of a new SODA operations type to find documents so that you can perform read and write operations on them.
Oracle Database provides the SODA_OPERATION_T
type (added in Oracle Database 18.3) to specify all read and write operations, except for inserts (which I covered in my first article on SODA and PL/SQL). It contains methods for finding documents in a collection, replacing those documents, and even removing a document from a collection.
The usual approach to take advantage of these operations is to call the FIND
method of SODA_COLLECTION_T. FIND
returns an instance of the operation type of the collection. You then use methods of SODA_OPERATION_T
to perform the desired actions.
The following are SODA_OPERATION_T
methods that perform read operations:
get_cursor
returns a pointer to a cursor that enables you to fetch all specified documents (through the API, not SQL).get_one
gets a single document.count
returns the count of documents that match your criteria.And the following are SODA_OPERATION_T
methods that perform write operations:
replace_one
replaces the contents of a document.replace_one_and_get
replaces the contents and returns the key to the new document.remove
removes the specified document.These methods are terminal methods: They are the last method you invoke in a chain of invocations to perform the read or write operation.
Every method call before that in the chain is a nonterminal method, which helps you get to precisely the document(s) you want to read or write. You can think of the nonterminal methods as all contributing in their way to a “WHERE
clause” applied to the search through the collection. These are the nonterminal methods:
key
specifies the key for the document you want to operate on.keys
provides a comma-delimited list of keys.Filter
uses JSON query-by-example syntax to specify documents of interest, an ordering of the documents returned, and more.version
specifies the document version for your find operation.skip
skips N documents and is used for pagination.limit
limits the retrieval to N documents and is also used for pagination.To perform operations on a document or a collection of documents, I first need to create that collection. Here’s the data I’ll be working with in this article:
DECLARE l_collection soda_collection_t; l_status PLS_INTEGER; BEGIN l_collection := dbms_soda.create_collection ('FriendsWithDocs'); l_status := l_collection.insert_one ( soda_document_t ( b_content => UTL_RAW.cast_to_raw ( '{"friend_type":1, "friend_name":"Lakshmi"}, "favorites": [{"song" : "Somewhere over the Rainbow"}, {"spice" : "tamarind"}, {"flavor" : "cherry"} ]}'))); l_status := l_collection.insert_one ( soda_document_t ( b_content => UTL_RAW.cast_to_raw ( '{"friend_type":2, "friend_name":"Sally"}, "favorites": [{"color" : "blue"}, {"flavor" : "chocolate"}, {"flower" : "rose"} ]}'))); l_status := l_collection.insert_one ( soda_document_t ( b_content => UTL_RAW.cast_to_raw ( '{"friend_type":2, "friend_name":"Jorge", "favorites": [{"color" : "green"}, {"flavor" : "chocolate"}, {"tree" : "oak"} ]}'))); END;
The data comes from three friends (friend_name
), of two different friend types (friend_type
)—I will leave it up to your imagination to choose a couple of types of friends—and it includes a variety of favorites (favorites
) in a nested JSON array.
Note: I use cast_to_raw
to convert my string to a BLOB. This is OK for demonstrations and small JSON documents but could cause issues with larger documents. A more robust (but also more complicated) solution, suitable for production, would be to use DBMS_LOB
subprograms.
Method chaining is a common practice in object-oriented programming but less familiar in the procedural world (and PL/SQL is a procedural language). When coding your read and write operations on documents, you will usually chain together several of the nonterminal methods listed previously.
First, here’s a block of code that does not use method chaining:
DECLARE l_collection soda_collection_t; l_operation soda_operation_t; l_cursor soda_cursor_t; l_status BOOLEAN; BEGIN l_collection := dbms_soda.open_collection ('FriendsWithDocs'); l_operation := l_collection.find (); l_cursor := l_operation.get_cursor (); l_status := l_cursor.close; END;
I start the block by opening my collection. Then I call the FIND
method to return an instance of the SODA_OPERATION_T
type for all the documents in the collection. (I will show you later in the article how to filter that FIND
if you don’t want them all). I then call the get_cursor
method to return a cursor I can use to iterate through the documents. Finally, because it is the right thing to do, I close the cursor.
Here’s that same functionality, now using the chained method approach.
DECLARE l_cursor soda_cursor_t; l_status BOOLEAN; BEGIN l_cursor := dbms_soda.open_collection ('FriendsWithDocs').find ().get_cursor (); l_status := l_cursor.close; END;
There is no need to declare the collection or operation instances. I simply “chain” the method calls together, using dot notation. So: fewer declarations, fewer lines of code. Each call to open_collection
involves a SQL query against the underlying tables. So if you plan to perform multiple find operations on the same collection, you should open it in one step, returning the collection instance, and then use that instance for multiple chained operations.
When you first start working with SODA elements, you might want to break these nonterminal methods out so you can more easily debug the steps in your chain. Once you are familiar with SODA, however, you will find the chained approach preferable.
The SODA API offers a cursor to make it easy for you to iterate through all documents returned by the FIND
and FILTER
methods.
DECLARE l_document soda_document_t; l_cursor soda_cursor_t; l_content_shown BOOLEAN := FALSE; l_status BOOLEAN; BEGIN l_cursor := dbms_soda.open_collection ('FriendsWithDocs').find ().get_cursor (); -- Loop through the cursor WHILE l_cursor.has_next LOOP l_document := l_cursor.NEXT; IF l_document IS NOT NULL THEN DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key); IF NOT l_content_shown THEN /* Just show content once to reduce output volume */ l_content_shown := TRUE; DBMS_OUTPUT.put_line ( 'Content: ' || json_query (l_document.get_blob, '$' PRETTY)); END IF; DBMS_OUTPUT.put_line('Creation timestamp: ' || l_document.get_created_on); DBMS_OUTPUT.put_line('Last modified timestamp: ' || l_document.get_last_modified); DBMS_OUTPUT.put_line('Version: ' || l_document.get_version); END IF; END LOOP; l_status := l_cursor.close; END;
This cursor block finds all the documents in a collection, returns a cursor to those documents, and then uses these cursor methods to iterate:
has_next
returns TRUE
if there is more to “fetch.”next
gets the next document.Note that for “basic” find operations in which order is not specified and no pagination methods are called, no ordering is applied to the documents. When calls to skip()
or limit()
are involved, the order of the documents is based on the internal key for that document, a universal unique identifier (UUID). I’ll explore how to override this sorting later in this article.
Here’s the output from running the above cursor block:
Document key: 29D6E98467EC4FA7BF18572986FF8925 Content: { "friend_type" : 1, "friend_name" : "Lakshmi", "favorites" : [ { "song" : "Somewhere over the Rainbow" }, { "spice" : "tamarind" }, { "flavor" : "cherry" } ] } Creation timestamp: 2019-05-03T18:39:46.787333Z Last modified timestamp: 2019-05-03T18:39:46.787333Z Version: 698637C8DC1A843E0078D77DD1680D17A4E8B9A8108B87081BE44902AD9484FD Document key: 6CFE30D607284F15BF0F59B1FD952842 Creation timestamp: 2019-05-03T18:39:46.787884Z Last modified timestamp: 2019-05-03T18:39:46.787884Z Version: D5B1E2370AB9A6C15FDCC0DB53E38D1D3B5306E57B039040E22BF7619A5166B1 Document key: BE2CA098F5554F5DBFF9454A8F02DEA4 Creation timestamp: 2019-05-03T18:39:46.788234Z Last modified timestamp: 2019-05-03T18:39:46.788234Z Version: 63A7781DBA6117B6CBACECF5CB9959054D1FF6D0852317701D9A10F9A43DABA4
Sometimes you’ll want to iterate through multiple documents, and sometimes you’ll want to get just one document. You can get that document by referencing a specific key.
Usually those keys are generated internally and passed back and forth between the database and the application. For purposes of the demonstration below, I create a collection and use the metadata
parameter to tell PL/SQL that I will provide the keys myself.
That way I know the key I can use to retrieve the document.
DECLARE l_document soda_document_t; l_collection soda_collection_t; l_metadata VARCHAR2 (4000) := '{"keyColumn" : { "assignmentMethod" : "CLIENT" }}'; l_status PLS_INTEGER; BEGIN l_collection := dbms_soda.create_collection ('MyOwnKeys', l_metadata); DBMS_OUTPUT.put_line ( 'Collection specification: ' || json_query (l_collection.get_metadata, '$' PRETTY)); l_status := l_collection.insert_one ( soda_document_t ('FriendLakshmi', b_content => UTL_RAW.cast_to_raw ( '{"friend_type":1,"friend_name":"Lakshmi"}'))); l_document := l_collection .find () .key ('FriendLakshmi') .get_one; DBMS_OUTPUT.put_line ('Key: ' || l_document.get_key); DBMS_OUTPUT.put_line ('Content: '); DBMS_OUTPUT.put_line ( l_document.get_key|| '+'||json_query (l_document.get_blob, '$' PRETTY)); END;
The output below shows first the information about the collection, verifying that the assignmentMethod
for the key is CLIENT
. Following that is the information for that document.
Collection specification: { "schemaName" : "HR", "tableName" : "MyOwnKeys", "keyColumn" : { "name" : "KEY", "sqlType" : "VARCHAR2", "maxLength" : 255, "assignmentMethod" : "CLIENT" }, "contentColumn" : { "name" : "JSON_DOCUMENT", "sqlType" : "BLOB", "compress" : "NONE", "cache" : false, "encrypt" : "NONE", "validation" : "STANDARD" }, "readOnly" : false } Key: FriendLakshmi Content: FriendLakshmi+{ "friend_type" : 1, "friend_name" : "Lakshmi" }
I’ve shown you how to get all the documents in a collection and a single document by key, but you have much more flexibility than that. You can also use JSON query-by-example syntax to filter your document retrieval.
You do this by adding the filter
method to the chained invocation, right after find
, as in
dbms_soda.open_collection ('FriendsWithDocs').find ().filter (l_filter).get_cursor ();
In the block below, I set the filter to find only those friends of type “2” (whatever that might be!).
DECLARE l_document soda_document_t; l_cursor soda_cursor_t; l_filter VARCHAR2 (128) := '{"friend_type" : "2"}'; l_status BOOLEAN; BEGIN l_cursor := dbms_soda.open_collection ('FriendsWithDocs').find ().filter (l_filter) .get_cursor (); WHILE l_cursor.has_next LOOP l_document := l_cursor.NEXT; IF l_document IS NOT NULL THEN DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key); DBMS_OUTPUT.put_line ( 'Content: ' || json_query (l_document.get_blob, '$' PRETTY)); END IF; END LOOP; l_status := l_cursor.close; END; Document key: 6CFE30D607284F15BF0F59B1FD952842 Content: { "friend_type" : 2, "friend_name" : "Sally", "favorites" : [ { "color" : "blue" }, { "flavor" : "chocolate" }, { "flower" : "rose" } ] } Document key: BE2CA098F5554F5DBFF9454A8F02DEA4 Content: { "friend_type" : 2, "friend_name" : "Jorge", "favorites" : [ { "color" : "green" }, { "flavor" : "chocolate" }, { "tree" : "oak" } ] }
I can also use the query-by-example syntax to search for documents whose nested array elements match a certain criterion. The code below finds and displays all friends whose favorite flavor is chocolate:
DECLARE l_document soda_document_t; l_cursor soda_cursor_t; l_filter VARCHAR2 (128) := '{"favorites.flavor" : "chocolate"}'; l_status BOOLEAN; BEGIN l_cursor := dbms_soda.open_collection ('FriendsWithDocs').find () .filter (l_filter).get_cursor (); WHILE l_cursor.has_next LOOP l_document := l_cursor.NEXT; IF l_document IS NOT NULL THEN DBMS_OUTPUT.put_line ('Document key: ' || l_document.get_key); DBMS_OUTPUT.put_line ( 'Content: ' || json_query (l_document.get_blob, '$' PRETTY)); END IF; END LOOP; l_status := l_cursor.close; END; Document key: 6CFE30D607284F15BF0F59B1FD952842 Content: { "friend_type" : 2, "friend_name" : "Sally", "favorites" : [ { "color" : "blue" }, { "flavor" : "chocolate" }, { "flower" : "rose" } ] } Document key: BE2CA098F5554F5DBFF9454A8F02DEA4 Content: { "friend_type" : 2, "friend_name" : "Jorge", "favorites" : [ { "color" : "green" }, { "flavor" : "chocolate" }, { "tree" : "oak" } ] }
I can also find all the documents whose keys match the provided list. I demonstrate this feature below by again specifying a document in which I can provide the keys myself. I do this with the metadata parameter and an assignmentMethod
set to CLIENT
.
DECLARE l_collection soda_collection_t; l_metadata VARCHAR2 (4000) := '{"keyColumn" : { "assignmentMethod": "CLIENT" }}'; l_keys soda_key_list_t; l_cursor soda_cursor_t; l_status BOOLEAN; l_document soda_document_t; BEGIN l_collection := dbms_soda.create_collection ('ColorKeys', l_metadata); l_status := l_collection.insert_one ( soda_document_t ( 'Red', b_content => UTL_RAW.cast_to_raw ('{"thing":"blood"}'))) = 1; l_status := l_collection.insert_one ( soda_document_t ( 'Green', b_content => UTL_RAW.cast_to_raw ('{"thing":"grass"}'))) = 1; l_keys := soda_key_list_t ('Green'); l_cursor := l_collection.find ().keys (l_keys).get_cursor (); WHILE l_cursor.has_next LOOP l_document := l_cursor.NEXT; IF l_document IS NOT NULL THEN DBMS_OUTPUT.put_line ('Key: ' || l_document.get_key); DBMS_OUTPUT.put_line ( json_query (l_document.get_blob, '$' PRETTY)); END IF; END LOOP; l_status := l_cursor.close; END;
And I see this output:
Key: Green
{
"thing" : "grass"
}
Suppose I want to change a document in my collection. Currently I will need to replace it with another. So I first create or receive the document; find the document, using its key; and use replace_one
or replace_one_and_get
.
In the block below, I use the same ColorKeys
collection created in the previous section and replace the Red
document with a Blue
document.
DECLARE l_collection SODA_COLLECTION_T; l_document SODA_DOCUMENT_T; l_new_document SODA_DOCUMENT_T; BEGIN l_collection := DBMS_SODA.open_collection('ColorKeys'); l_document := SODA_DOCUMENT_T( b_content => utl_raw.cast_to_raw('{"Blue" : "Bluebird"}')); l_new_document := l_collection.find().key('Red').replace_one_and_get(l_document); IF l_new_document IS NOT NULL THEN DBMS_OUTPUT.put_line('Document:'); DBMS_OUTPUT.put_line('Key: ' || l_new_document.get_key); END IF; COMMIT; END;
If I do not need the result document (the document returned by and_get
methods), I can call the replace_one
method. That replaces the specified document and returns a status value: 1 if the document was replaced, 0 otherwise.
My previous article on SODA showed you how to remove a single document. You can now use a remove
method in SODA_OPERATION_T
to remove one or many documents, once you have found, via key or filter, the document or documents you wish to remove. Here is an example of single-document removal:
DECLARE l_collection SODA_COLLECTION_T; l_status NUMBER; BEGIN l_collection := DBMS_SODA.open_collection('ColorKeys'); l_status:= l_collection.find().key('Green').remove; IF l_status = 1 THEN DBMS_OUTPUT.put_line('Document removed'); END IF; END;
And in the block below, I use query-by-example to remove all documents from FriendsWithDocs
whose friend_type
is equal to 2.
DECLARE l_collection SODA_COLLECTION_T; l_status NUMBER; BEGIN l_collection := DBMS_SODA.open_collection('FriendsWithDocs'); l_status:= l_collection.find().filter('{"friend_type" : "2"}').remove(); IF l_status = 1 THEN DBMS_OUTPUT.put_line(Friend type 2 documents removed'); END IF; END;
Suppose your collection has 1,000,000 documents and you want to work with batches of 100 documents at a time. SODA_COLLECTION_T
offers the ()
and limit()
nonterminal methods to help you get the job done.
In the following block, I create a collection and stuff it full of 100 very simple documents. I then use find()
, skip()
, and limit()
to skip the first 50 documents and get just the next 5.
DECLARE l_collection soda_collection_t; l_document soda_document_t; l_cursor soda_cursor_t; l_status BOOLEAN; BEGIN l_collection := dbms_soda.create_collection ('SillyCollection'); FOR indx IN 1 .. 100 LOOP l_status := l_collection.insert_one ( soda_document_t ( b_content => UTL_RAW.cast_to_raw ( '{"myIndex":' || indx || ',"myValue":"' || CASE MOD (indx, 2) WHEN 0 THEN 'Even' ELSE 'Odd' END || '"}'))) = 1; END LOOP; l_cursor := l_collection.find ().skip (50).LIMIT (5).get_cursor; WHILE l_cursor.has_next LOOP l_document := l_cursor.NEXT; IF l_document IS NOT NULL THEN DBMS_OUTPUT.put_line ( 'Content: ' || json_query (l_document.get_blob, '$' PRETTY)); END IF; END LOOP; l_status := l_cursor.close; END; Here’s my output: Content: { "myIndex" : 89, "myValue" : "Odd" } Content: { "myIndex" : 48, "myValue" : "Even" } Content: { "myIndex" : 50, "myValue" : "Even" } Content: { "myIndex" : 2, "myValue" : "Even" } Content: { "myIndex" : 60, "myValue" : "Even" }
But wait! Why don’t I see 51 through 55? That’s because results are ordered only by the document key values (which are by default generated automatically) unless skip()
or limit()
appears in method calls.
If you need to change the ordering, use the filter
method and specify an orderby
. In the block below, I order by the myIndex
value. I specify that the datatype
is a number
to ensure the correct ordering (it will otherwise be treated as a string).
DECLARE l_collection soda_collection_t; l_document soda_document_t; l_cursor soda_cursor_t; l_status BOOLEAN; BEGIN l_collection := dbms_soda.open_collection ('SillyCollection'); l_cursor := l_collection. find(). filter('{"$orderby" : [ { "path" : "myIndex", "datatype" : "number", "order" : "asc"}]}'). skip(50).limit(5).get_cursor(); WHILE l_cursor.has_next LOOP l_document := l_cursor.NEXT; IF l_document IS NOT NULL THEN DBMS_OUTPUT.put_line ( 'Content: ' || json_query (l_document.get_blob, '$' PRETTY)); END IF; END LOOP; l_status := l_cursor.close; END;
And I now see the following output:
Content: { "myIndex" : 51, "myValue" : "Odd" } Content: { "myIndex" : 52, "myValue" : "Even" } Content: { "myIndex" : 53, "myValue" : "Odd" } Content: { "myIndex" : 54, "myValue" : "Even" } Content: { "myIndex" : 55, "myValue" : "Odd" }
One thing to keep in mind when you are doing pagination and sorting: The process can be slow if you have lots of documents and you paginate through all of them. Whenever possible, try to restrict the set of documents in advance. Do this with a more complex query-by-example specification, such as
qbe = '{"$query" : {"salary" : {"$between" : [10000, 20000]}}, "$orderby" : {"someField" : 1}}';
SODA for PL/SQL will probably never be a “mainstream” feature set for Oracle Database developers. The majority of our work is likely to remain centered on traditional, extremely flexible, proven relational database objects, such as tables and views.
But as more and more UI developers, used to working with JavaScript and document databases, learn that they can also get the job done with Oracle Database, demand for these features and the skills to use them properly will increase.
READ “SODA and PL/SQL.”
READ an introduction to SODA.
LEARN more about SODA for PL/SQL.
WORK with the LiveSQL script for this article.
Illustration by Wes Rowell
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.