I have an interesting data set, courtesy http//www.citibikenyc.com. They have a few open feeds of JSON, including information regarding their stations.

http://gbfs.citibikenyc.com/gbfs/en/station_information.json

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.

With a decent text editor, I was able to do this in about 10 seconds.

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.

We’re going to launch this and point to our modified .json file.

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.

Our array items are now displayed as rows, here’s a preview.

The next step will be to confirm HOW the data is being stored, including the name of the TABLE.

We’ll default the table name to the name of the json file.

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.

Stations that have a capacity of more than 35 or has a surcharge waiver for electric bikes.

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.

In 21c there is a native JSON type you can choose as well.

Actually creating the Collections…via REST

PUT ords/{schema}/soda/latest/CollectionName

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.

Detailed Steps Here

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.

More than 1200 documents loaded in less than a second, from my house to Denver, over VPN.

Looking at the data in SQL Developer Web

Brand new feature in version 20.3, you can now browser, query, edit, etc your JSON documents using SQL Developer Web. We have some UI improvements I’m testing for version 20.4, so my screen may look a BIT different than yours in 20.3, but it’s basically the same.

Same query more or less we did with the SQL, but now using Query By Example (QBE.

QBE is documented with examples here, but here’s what I used:

{
  "$or": [
    {
      "capacity": {
        "$gt": 30
      }
    },
    {
      "electric_bike_surcharge_waiver": true
    }
  ]
}

With 20.4, you can look forward to Query History for the QBE’s, so you won’t have to memorize these so much 🙂

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.

The json data has a record in it and then some nested records too…

Full scenario here with CREATE table and data and the queries.

A SQL query including JSON…

Note if the attribute you are querying doesn’t exist, you just get a NULL returned for that value

Beda, our JSON Architect here at Oracle, has some nice reference posts on the subject, and I rely on them quite frequently.

thatjeffsmith
Author

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

Write A Comment

RSS
Follow by Email
LinkedIn
Share