Translating JSON Doc Store Query By Examples (QBE) to SQL!

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 –

There’s no SQL here, at least on the surface…screenshot via SQL Developer Web v20.4

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.

If we peeked into the BLOB, we’d see our {JSON} document.

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…