JSON persistence for Oracle

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

1 Like

Ticket 37461 created.

Follow-up to ticket:

I may be missing something in the related documentation, but I am
concerned about the upgrade path for existing users. They should be able
to upgrade Django without being required to migrate their existing
`JSONField`s immediately.

Could we provide a way to opt in to the native JSON type, perhaps with a
deprecation period if we intend to eventually remove support for the
existing storage type? One possibility might be a transitional setting
that lets existing projects retain the current storage type until they
have completed the migration. The default and deprecation timeline would
need consideration.

@sarahboyce

We didn’t say they have to use JSON data type, we said, we recommend :slight_smile:

So the following are the possible cases:

  • database is 19c, storage is BLOB (preferably), if they upgrade Django, they have to keep that data type, no data migration
  • database 26ai, storage is JSON (preferably), if they upgrade Django, they have to keep that data type, no data migration
  • database is 19c, storage is BLOB, if they upgrade the database to 26ai, they can keep that data type but they will not benefit from all the optimizations
  • later, they can migrate from BLOB to JSON and we have tools to do so

Now how is it controlled from Django standpoint, is it automatic (meaning, restart Django and run as usual but now managing JSON columns) or is it manual via some Django settings to force the usage of JSON data type when data has been migrated, the question remains.

Now to answer another question, being able to choose your own way of storing JSON would be nice. (keep CLOB if you like it, or BLOB, or JSON if the database version supports it).

HTH

Ah, sorry, I think I misunderstood. Are you proposing that Django’s JSONField should support multiple storage types on Oracle, with a preferred type for newly created columns depending on the database version, while existing columns continue to work without conversion?

I think so :slight_smile:

Note however that once you start for instance supporting JSON data type, queries with SQL/JSON functions (JSON_OBJECT(), JSON_ARRAY(), JSON_TRANSFORM(), etc.) should also internally work with JSON, meaning RETURNING JSON or else, it would also mean the processing would fallback to CLOB in the case or Oracle.