How do you usually handle JSON files in the Database?

User_RFKSX

Hi,

at the costumer I'm working for, for the first time we'll have to load JSON files at the end of an ETL. Usually, after loading a JSON document into a CLOB column of an Oracle table, are its elements extracted for being inserted in a normalized manner into different tables so as to be used for the future in a traditional relational way or is the JSON document left inside the CLOB column for being handled whenever you need?

What I'd like to know is if generally it's better transform as soon as possible the content of a CLOB containing a JSON structure in a group of values to be inserted into relational tables or if you sometimes maintain JSON structure like a real document in the database.

Oracle version 12c

Thanks in advance!

Mark

MaxOrgiyan-Oracle

Hi Mark,

Unless you have a specific need to shred the JSON document into relational columns, it's best to keep it intact. The advantage is that you get schema flexibility - as the application changes, you can add or remove fields in your JSONs as needed, without having to alter tables to add/remove new relational columns. Also, you get to store the data in its original format (i.e. JSON), and then you can also ship it out in the same format.

The recommended storage column is BLOB (as opposed to CLOB) on database releases below 21c. On database release 21c (currently only available on autonomous free tier), there's a new Oracle type for JSON specifically, called JSON type.

There are a bunch of different operators that allow you to query JSON data from SQL, such as json_value, json_query, json_table, and so on. For examples, see here.

Which 12c version are you on? The JSON support mentioned above was introduced in 12c, and there have been many bug fixes since that release. It's best to upgrade to a more recent release for JSON usage, if at all possible. 19c is best, as it's a long term release and has all the bug fixes.

Other things that might be of interest:

(1) SODA API. These are the JSON NoSQL style APIs for Oracle Database, implemented in various languages (Java/node.js/python, and built into ORDS). They allow you to work with JSON in Oracle in a very easy way, without using SQL.

(2) Autonomous JSON cloud service. This is all of the above, on the cloud. There's a free tier version you can use to play with it.

Let us know if you need more info.

Thanks,

Max.