Fastest Way to Upload a bunch of Documents to your Oracle JSON Collection?

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.

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.

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.

This documented here.

I want to load docs in there now.

This is documented here.

So ORDS ships with examples, including sample data/json files.

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.

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.

If you’re wondering what a GUI for this JSON stuff would look like, maybe you can get a preview here.