This is particularly relevant if the database character set is the Oracle-recommended value of A元2UTF8. 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). JSON objects are strings, and are treated like this in an Oracle database. 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. 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. 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.
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. 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. 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?”. 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. It would have been possible, with a clever table structure und a bit of denormalization. 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.
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. 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. 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. Notice the row without any JSON data isn't displayed. Phone VARCHAR2(50 CHAR) PATH ContactDetails.Phone,Īctive VARCHAR2(5 CHAR) PATH Active)) jt Last_name VARCHAR2(50 CHAR) PATH LastName,Įmail VARCHAR2(100 CHAR) PATH ContactDetails.Email, SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.activeĬOLUMNS (first_name VARCHAR2(50 CHAR) PATH FirstName, Notice the third row has no JSON data included.ĬONSTRAINT json_documents_pk PRIMARY KEY (id),ĬONSTRAINT json_documents_json_chk CHECK (data IS JSON)ĮXEC DBMS_STATS.gather_table_stats(USER, 'json_documents') Using JSON_TABLEĪ typical example of using JSON_TABLE to access the data might look like this. JSON_TABLE Enhancements in Oracle Database 18cĬreate and populate the following table to provide some JSON data to work with.JSON Support in Oracle Database 12c Release 1 (12.1.0.2) - JSON_TABLE.The SQL NESTED clause allows us to write the equivalent of the LEFT OUTER JOIN JSON_TABLE using a simplified syntax. Home » Articles » 19c » Here SQL NESTED Clause Instead of JSON_TABLE in Oracle Database 19c