I have an interesting data set, courtesy http//www.citibikenyc.com. They have a few open feeds of JSON, including information regarding their stations.
So when I’m looking at data, the first thing I want to do is play with it. Any my playground for all things data is…wait for it…an Oracle Database! One of the benefits you get from using Oracle is our ‘converged’ offering of data storage formats, all wrapped with common SQL, PL/SQL, and REST implementations.
You have AT LEAST 3 options for working with this data in an Oracle Database, and I’m not here to tell you which one of these is ‘best’, ‘quickest’, etc. That’s up for YOU to decide. I will say this though – aim for the path that best follows the skillsets of your developers.
Option 1: Tables, rows, and SQL!
This will be the most familiar option for an Oracle veteran.
If we look at SQL Developer Web, it can ingest the JSON and split the array items as new rows in a relational table. And even better, it will create the table for you!
Before we get started, I need to do a bit of ‘trimming’ to the JSON I’ve downloaded from CitiBikeNYC.
Instead of a nested data json doc composed of an array of stations, I’ve turned it into just an array of json documents.
SQL Developer Web, Data Loading
- ORDS is configured for your database
- SQL Developer Web is enabled
- Your schema is REST Enabled
- Watch the video
In the SQL Worksheet, the toolbar button has a Data Loading action.
As soon as you select this, you’ll be prompted for a file. The larger the file, the more patient you need to be, but this only took a few seconds.
The next step will be to confirm HOW the data is being stored, including the name of the TABLE.
The only ‘tricksy’ things here are that two of the attributes can’t be decomposed to columns. So ‘rental_methods’ and ‘eightd_station_services’ are both coming in as CLOBS with a ‘IS JSON’ check constraint. So our new table will have some JSON in it.
After you finish the wizard, you’ll see your new table is available, and you mastery of SQL will make chopping up this data pretty trivial.
Note that in the Oracle SQL world, there is no BOOLEAN, so JSON BOOLEAN’s come over as an NUMBER with 0=FALSE, 1=TRUE.
Option 2: Collections, Documents, and QBE!
The API for this solution in Oracle is known as SODA, or Simple Oracle Document Access. I’ve been talking about that more here lately on the REST side of things, but I’ve also covered the SODA command support in SQLcl as far back as 2016!
- ORDS is available for your database (SODA for REST endpoints)
- Your USER has the SODA_APP role
Create the Collection
Instead of creating a table, we create a COLLECTION to store our JSON Documents. The super-sneaky thing we do though is…implement your Collection as a TABLE.
I’ve done this already, so I’ll cheat and show you the AFTER before the…BEFORE.
Here’s a TABLE storing our JSON Documents. There’s an ID field, and a column for storing the document itself, in case a BLOB.
Actually creating the Collections…via REST
Since we’re using SODA for REST in this step to load the documents, we’ll also use SODA for REST to Create the Collection.
Once the collection is ready, we can do a POST to it, and use the ?action=insert qualifier. Using that same modified JSON file, we can tell ORDS to load each item in the array as a NEW document. You don’t have to do this, we could instead load it as a single document. That’s up to you to decide which way is best for your project.
POST https://...oraclecloudapps.com/ords/admin/soda/latest/{collection_name}?action=insert
Looking at the data in SQL Developer Web
You can browse, query, edit, download, etc your JSON documents using SQL Developer Web.
QBE is documented with examples here, but here’s what I used:
{
"$or": [
{
"capacity": {
"$gt": 30
}
},
{
"electric_bike_surcharge_waiver": true
}
]
}
Option 3: A little of both worlds, please
We saw this a bit in option 1, but I wanted to call it out explicitly.
- an Oracle TABLE
- one or more columns with an IS_JSON Check constraint
So in a row, you have a mix of both relational data, and JSON documents. And you can use SQL to query all of it.
Full scenario here with CREATE table and data and the queries.
A SQL query including JSON…
Beda, our JSON Architect here at Oracle, has some nice reference posts on the subject, and I rely on them quite frequently.
