If you’re of a certain age, you probably remember those books where you could interact with the story. Should Jill go left or right?

If you chose left, you’d find out that Jill got attacked by hungry wolves on page 91 and died, and so you’d backtrack to the beginning and go right instead.

Well, the Oracle Database also offers a myriad of choices when working with JSON structured data! You can store the JSON via …

  • relational tables, and VARCHAR2, CLOB, or BLOB columns
  • relational tables, 12c or higher, storing them as VARCHAR2, CLOB, BLOB with an IS_JSON CHECK constraint – gives you access to a ton of built-in database JSON functions and features
  • using the database as a JSON Document Store and the accompanying SODA APIs to work with your JSON documents
  • 21c ups the ante and offers a native JSON data type for improved performance and features

It’s a lot to chew on for sure. But the good news is that Jill will live and propser no matter which way you choose to store your JSON data!

What’s the best way to go? Well, it depends on your use case and the skillset and strengths of your developers.

The Video!

It’s just under an hour.

Importing your JSON data as a relational table, and use SQL to get your data out?

My array is unnamed, and I want the second element of the array, they start at 0.

Our tools make it easy to get your JSON data, either parts or all of it, into a COLUMN of your TABLEs. We’ll show the different permutations, and do some quick SQL to play with the data.

Or do I store the JSON data as documents and use one of the many SODA APIs?

This “starring” array element doesn’t existing in every doc, so just give me docs that have it.

Not much SQL involved here, at least not if you don’t want to use it. You can simply keep your JSON hat on, and ask for the data by describing the patterns you’re looking for using more JSON. But yes, you CAN ALSO USE SQL, and we’ll show that as well!

Our Oracle Database Tools team has provided plenty of solutions and interfaces, regardless of the type of JSON adventure you’ve chosen to undertake. We want you to understand those options, hence the LiveLab and this webinar.

Explore your JSON options with us, the slides!

Our event has already occurred, WAS recorded, and I’ll share that video here shortly.

Brian and I took our adventurers through the different scenarios with our tools like SQLcl, ORDS, and Database Actions (SQL Developer Web) for loading and working with your JSON data in the database.

Slides from our talk with working links…

The webinar also included the debut of our new JSON LiveLab, which takes you through the technology, step-by-step, with plenty of screenshots so you can easily follow along.

Oracle LiveLabs & Workshops

Our brand new JSON Adventure Lab is now available! You’ll be working with the same JSON docs Brian and I used in our video presentation and slides. You’ll see how to import them to a new relational table or as a new JSON Document Collection.

You’ll then see how to use SQL or the SODA APIs to work with your data.

The Labs even have built-in support for provisioning a free Autonomous Cloud database service, borrowing one of ours, or you can use your own local Oracle Database if you have one.

The JSON Lab – Click Here.

There are dozens of these self-guided courses you can take to teach yourself the newest Oracle Database technology. JSON is just one of the topic areas.

Bonus: We’ve already published a REST Workshop that takes you through creating a user, loading some data to a table, REST Enabling it, and securing the end points with OAuth2.


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