I learned something new today, and I wanted to share it right away.
We’ve been talking about using the Oracle Database as a JSON Document Store for awhile now, but the general idea is…like the schemaless promise of Mongo, but want all the power of Oracle Database (including SQL?) – that’s the Converged Oracle Database!
With the SODA interface, you can work with Collections and Documents. There are drivers for the various programming languages, commands you can run in SQLcl or SQL Developer (SODA), and there are REST APIs.
Querying documents with QBE
QBE’s allow you to describe what your documents should look like.
If we use the all-new interface in SQL Developer Web, that could be as simple as this –
In this QBE search, I’m asking for any document that has a nested LineItems json with a Part.UPCCOde of 1302015692.
There’s a table there, somewhere
In the database, in our HR schema, I can find a table called “BlogPostExample”, and it looks like this:
The BLOBs are the documents from our collection.
Ok, so there’s a table there, so surely we can find our documents with SQL instead of a QBE, right?
Yes, of course, and there’s a great set of JSON friendly SQL functions you can use, including JSON_EXISTS().
Now, for the ‘trick’
The Trick: Translating the QBE to SQL
We’re going to use the SODA for REST APIs, but, we’re going to add an extra parameter, ‘sqlStatement=true’
localhost:8080/ords/hr/soda/latest/BlogPostExample?action=query&sqlStatement=true
We have a “sqlStatement” nested json attribute! And in there is the SQL that was used to satisfy the POST request.
If I take that SQL out, and strip out the control/escape characters, then I have something like this:
select /*+ FIRST_ROWS(101) */ "JSON_DOCUMENT" , "ID" , "LAST_MODIFIED" , "CREATED_ON" , "VERSION" from "HR"."BlogPostExample" where json_exists ( "JSON_DOCUMENT" format json, '$?(@.LineItems.Part.UPCCode == $B0)' passing '13023015692' as "B0" ) order by "ID" fetch next 101 rows only
And if I run it, then I get the same results as I saw in my browser and REST client, I just need to go peek into the BLOBs…
This is a pretty simple example. You’ll get much more interesting queries out as you get more interesting QBEs.
Like in a previous post…
Thanks to one of our JSON architects, Douglas McMahon for the pointer!