Hi,
I wanted to bring some clarifications to the evolution of JSON persistence inside Oracle. That would probably lead to lower storage footprint, better performance and increased support for related capabilities.
A/ Pre-19c:
In 12c, JSON stored as text was introduced (2014). One can store JSON data using VARCHAR2 , CLOB and BLOB columns using the IS JSON check constraint.
Note that the JSON standard specifies that JSON are stored as UTF-8.
CLOB (and NCLOB btw) are stored as UTF-16. Meaning, for 1-byte encoded UTF-8 chars, you now store them using 2 bytes!
- Insert: UTF-8 => UTF-16 conversion
- Select: UTF-16 => UTF-8 conversion
The performance impact is measurable when storing JSON as [N]CLOB.
In this version, the MAX_STRING_SIZE database parameter was also introduced to let you have VARCHAR2 columns with up to 32767 bytes. So for mid-sized JSON documents, you could store them using this data type.
=> Reco: use BLOB or VARCHAR2
ps: I’ll not talk about storing JSON in a RAW column (I don’t remind the exact version this is supported)
B/ 19c:
In 19c, among other changes, you could store JSON data in BLOB column but using the binary encoding (code name OSON). This was done as an important optimization for the Autonomous JSON Database (AJD) cloud service release; and is solely supported in 19c on AJD.
This binary encoding was initially introduced in 2017 in 12cR2 for the support of in-memory columnar processing (SIMD CPU instructions) for JSON.
Recently OSON has been open-sourced and started to be exposed using a native data type…
C/ 21c:
As an innovation release (not a long term support release), 21c introduced (among other innovations) the JSON data type. This new data type leverages the OSON binary encoding by default. There were other optimizations like inline threshold increase (from 4k to 8k) but already JSON/OSON support proved to bring increased performance.
D/ 23c/23ai/26ai:
First, there is no more 23c nor 23ai, the name has been replaced with 26ai; and this release made the JSON data type the default format to be considered when working with JSON data.
It brings OSON binary format version 4, optimizations solely for 26ai like block size based inlining, additional check constraints, advanced compression support, optimized operators (defaulting to JSON output as much as possible), Exadata offloading (when the infrastructure is present, and of course when you use Autonomous Database cloud services since they inherit this infrastructure), …
26ai also provides a DBMS_JSON.JSON_TYPE_CONVERTIBLE_CHECK method to ease transitioning from Text based storage to Binary storage.
26ai also introduced the support for JSON schema (draft-12 and incoming v1) and using the database vocabulary allows to assist the database in storing JSON fields using SQL native data types (example: “2026-10-27T12:17:03Z” as TIMESTAMP vs just a string). This can further improve SQL query performance as parsing is completely avoided (storage is also reduced).
There are of course numerous other improvements worth discussing like JSON Relational Duality Views, Multi-Value indexes, Full-Text indexes, etc. but the message is rather clear: starting with 26ai, go with JSON data type unless you have incredibly huge documents (> 32 MB after OSON encoding/compression) and in that case, go with BLOB, not [N]CLOB.
Any question? Feel free to ask/comment.
Thanks