Notice the row without any JSON data isn't displayed. The previous statement is functionally equivalent to the following.Īctive VARCHAR2(5 CHAR) PATH Active)) jt ON 1=1 This is because we are effectively doing an inner join. If we want to show data from columns outside the JSON data column, even when the JSON data column is null, we need to do a left outer join to JSON_TABLE, as shown below. JSON Support in Oracle Database 12c Release 1 (12.1.0.While working with a customer, a usecase came up that was hard to implement with a relational database (due to NDA, I cannot share the usecase itself).SQL NESTED Clause Instead of JSON_TABLE.This doesn't stop us using the NESTED clause to access nested columns or lists of columns. It was necessary to store some information for every row in the Oracle database, but although the structure of the data was similar, it contained a lot of special cases and different hierarchies. It would have been possible, with a clever table structure und a bit of denormalization. In fact, we did a PoC with a relational model, but it soon turned out that reads were slow and the queries to filter by this data grew more and more complex. There were good and productive discussions about the best approaches, and at some point, somebody said “This would be a good usecase for a document DB, wouldn’t it?”. Unfortunately, installing infrastructure for this was out of the picture, as the customer did not have experience with this technology and we were not keen on maintaining even more infrastructure, but we agreed on creating another PoC with the whole hierarchy being stored as a JSON in Oracle DB. The PoC was successful and the solution is productive and performing well.Īll the informtion in this post is compiled from the comprehensive JSON Developer’s Guide for Oracle 19c. As Oracle added a lot of funtionality to its JSON features in the latest versions, the approaches described here may not be working on other Oracle Database versions. I decided to write this up as a lessons-learned kind of post, because we ran into some issues which are documented, but might not be easy to find. Here is what I learned: Creating a JSON column in Oracle Which data type to choose? You do not need deep Oracle knowledge to understand the article, knowing basic SQL is sufficient. JSON objects are strings, and are treated like this in an Oracle database.

Oracle recommends CLOB with the following explanation: If we want to include a column with JSON data into a table, there are several data types we can choose from:Īs VARCHAR2 columns have limits, and JSON data we’d like to store is very dynamic, I’d rule out VARCHAR2, leaving CLOB (Character Large OBject) and BLOB (Binary Large OBject).

This is particularly relevant if the database character set is the Oracle-recommended value of A元2UTF8.

Oracle 19c json query example