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.

If we ‘peek’ into the JSON_DOC column, in our table, we can see the actual JSON.

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
What’s in there overall, how many, and maybe a quick query?

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;
/
GRANT SODA_APP to HR;

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.

After the PUT runs, I can see the new Table in the database.

I want to load docs in there now.

This is documented here.

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

POList.json – what’s that?
The file itself is an array of 70 JSON ‘records’ – here’s a look at one of them.

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.

The ?action=insert is very important here!

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.

Ta-da.

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

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

Write A Comment