JSON – it’s the new whiz bang.
JavaScript has taken off, and you don’t need to take my word for it.
And with JavaScript comes JSON. There are so many libraries on GitHub that are about turning data into really slick web and mobile applications. We have our own set of libraries available – have you heard of Oracle JET?
So JSON is here for awhile. What can you do with it? Well, you know what you can do already with the Oracle Database. What if you could harness the power of our database engine and our SQL layer, but apply that to all the JSON you have laying around?
That’s the core idea behind the Oracle Document Store. In 12c (12.1.0.2) you can store JSON in an Oracle table, and work with it as JSON vs text that you’d have to manipulate manually.
And one of the interfaces we provide for this is known as Simple Oracle Document Access (SODA). We provide both REST and Java implementations for doing SODA.
AND, we also provide support for SODA in Oracle SQLcl.
If you want to create a new collection of documents, you can simply use the SODA CREATE command.
Actually, if you want to do any sort of SODA work, SQLcl supports that.
When you create a collection in SODA, it actually goes and creates a table in your schema to hold it.
Let’s Go Back to the CLI
If you want to quickly create some collections, query them, update them, delete them, or whatever – the entire SODA api is available in SQLcl. I’m not going to demo all of it for you, but if you’re interested in SODA, just know that SQLcl is already and waiting to help you.
Let’s add to our SODA collection.
Now let’s see what we have in there.
One of the concepts I found immediately alluring was the notion of ‘Query By Example’ (QBE). Here I can use a bit of JSON to tell SODA what I’m looking for in the collection.
For what it’s worth, I’ve downloaded my tweets and stored them in the Oracle Document Store for these examples.
I hope you have an idea now of what’s available to you.
Before you run away, a few things:
- SODA requires Oracle Database patch 20885778 to be installed on your 12.1.0.2 Oracle Database
- Your Oracle user needs the SODA_APP privilege to be granted
- You really, really need to follow Beda’s blog on all things SODA/JSON
- Kris talks about SODA in SQLcl here, and also provides an idea of what you can do with our SQLcl JavaScript too
