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 , 3 weeks ago
| Cc: | added |
|---|
comment:2 by , 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/
- 26ai doc: https://docs.oracle.com/en/database/oracle/oracle-database/26/adjsn/tables-json-columns.html#GUID-1B25FDB0-42A8-44E0-85F9-D09814DDF10B
- 21c doc (feature does not exist): https://docs.oracle.com/en/database/oracle/oracle-database/21/adjsn/creating-a-table-with-a-json-column.html
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 , 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 , 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 , 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 , 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
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