Not ONLY might this be the fastest, it might ALSO be the easiest way.
When working with JSON in Oracle Database, I have two options, I can go with columns in a table with the ‘IS JSON’ check constraint and use SQL – or I can use the SODA code path, and stick with SODA commands and/or the REST APIs.
I like to seed my JSON Collections with a ‘bunch’ of documents, so how do I get there quickly?
If you were thinking, I bet he’s going to pivot to REST and ORDS again, you’d be right!
In just two simple REST calls, I can create my collection and load up my 70 documents.
Here’s the call to do the load (Docs):
curl -X POST --data-binary @POList.json -H "Content-Type: application/json" http://localhost:8080/ords/schema/soda/latest/MyCollection?action=insert
ORDS automatically supports the SODA REST APIs, so you can do things like create collections, add documents, or in this case, bulk load a bunch of documents to a collection, in a single call!
Starting from the beginning
What is SODA?
Simple Oracle Document Access (SODA) is a collection of commands and REST APIs that we provide to make it easy for you to treat the Oracle Database as a JSON Document store.
So instead of thinking of tables and rows, you can think in terms of collections and documents. Behind the scenes – we have an Oracle Table there to store the documents in your collection.
![](https://www.thatjeffsmith.com/wp-content/uploads/2020/10/image-16-1024x599.png)
Looking at my ‘ORDSExamples’ Collection, I can see in the database that there are 71 rows in the table, so I then I also have 71 Documents.
The SODA Command
I’ve talked about this before – you can run through your collections and documents in SQLcl, for example.
![oracle document store json soda sqlcl](https://www.thatjeffsmith.com/wp-content/uploads/2016/12/soda.png)
SODA for REST APIs
Instead of the SODA command, let’s use HTTPS and GET, PUT, POST, PUT, and DELETE to do stuff with our Collections and Documents.
Before I can create a collection, I need to have a REST Enabled Schema, and my user for that schema needs the SODA_APP privilege.
BEGIN ORDS.ENABLE_SCHEMA(p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); COMMIT; END; /
Once this is going, you can start making REST calls to this base URI
https://server:port/ords/hr/soda/latest/
Let’s create a collection.
![](https://www.thatjeffsmith.com/wp-content/uploads/2020/10/image-17.png)
I want to load docs in there now.
So ORDS ships with examples, including sample data/json files.
![](https://www.thatjeffsmith.com/wp-content/uploads/2020/10/image-18-1024x435.png)
![](https://www.thatjeffsmith.com/wp-content/uploads/2020/10/image-19.png)
What I would like to do next, is take this one file of 70 records, and have them added as 70 new documents in my new collection.
![](https://www.thatjeffsmith.com/wp-content/uploads/2020/10/image-20-1024x584.png)
Without the ?action parameter passed, this would go in as a single new document to my BlogPostExample Collection.
Now I can start using my Query By Example (QBE) calls to see what’s in there.
![](https://www.thatjeffsmith.com/wp-content/uploads/2020/10/image-21.png)