Opened 3 weeks ago

Last modified 12 days ago

#37461 new New feature

Prefer JSON data type as JSON storage starting with Oracle 26ai

Reported by: Loïc LEFEVRE Owned by:
Component: Database layer (models, ORM) Version: 6.1
Severity: Normal Keywords: JSON
Cc: Loïc LEFEVRE, Sage Abdullah, Christopher Jones, Mariusz Felisiak Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

As a follow-up to ​https://forum.djangoproject.com/t/json-persistence-for-oracle/46282, the JSON data type should be preferred over CLOB when storing and processing JSON documents.

Change History (6)

comment:1 by Sarah Boyce, 3 weeks ago

Cc: Sage Abdullah Christopher Jones Mariusz Felisiak added

Thank you for the ticket!

Is there a reason to limit this to Oracle 26ai, given that the native JSON data type was introduced in 21c?

We would also need to consider existing JSONFields. Ideally, these would continue to work without requiring an immediate data migration, perhaps with a documented conversion path for users who want to adopt the native type?

I've cc'd a few folks who may have thoughts or concerns about this change

comment:2 by Loïc LEFEVRE, 3 weeks ago

I spoke about 26ai because 21c is getting to be desupported completely by end of July 2027. But you may include it as support for this JSON native data type.

However, be aware that not all JSON data type related features would be supported such as JSON column type limits:

CREATE TABLE j_purchaseorder 
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   data        JSON (OBJECT));

raises:

Error starting at line : 4 in command -
CREATE TABLE j_purchaseorder
  (id          VARCHAR2 (32) NOT NULL PRIMARY KEY,
   date_loaded TIMESTAMP (6) WITH TIME ZONE,
   data        JSON (OBJECT))
Error report -
ORA-00907: missing right parenthesis
Help: https://docs.oracle.com/error-help/db/ora-00907/

Regarding data migration, the link from the referenced forum post deals with it: ​https://oracle-base.com/articles/23/migrating-text-based-json-to-json-data-type-pre-migration-check-23

comment:3 by Loïc LEFEVRE, 3 weeks ago

And for the official recommendation, please see: ​https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/migrating-textual-json-data-json-data-type.html

Oracle recommends that you use native binary JSON data (type JSON), rather than textual JSON data (type VARCHAR2, CLOB, or BLOB).

comment:4 by Sarah Boyce, 12 days ago

Resolution: → needsnewfeatureprocess
Status: new → closed
Type: Cleanup/optimization → New feature

I think we should perhaps treat this as a new feature.

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 JSONFields 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.

I'll close this for now pending discussion and agreement of an upgrade path on the forum.

comment:5 by Tim Graham, 12 days ago

There is some prior art: #33507 changed UUIDFIeld to use the native UUID data type on MariaDB 10.7+.

comment:6 by Sarah Boyce, 12 days ago

Resolution: needsnewfeatureprocess
Status: closed → new
Triage Stage: Unreviewed → Accepted

Thanks Tim, that is really helpful

Tentatively accepting. This is outside my area of expertise and so I am very happy to be overruled here
The implementation needs to address compatibility with existing JSONFields and document any required upgrade steps

Note: See TracTickets for help on using tickets.
Back to Top