Juice up your JSON on Autonomous

July 14, 2022

1–2 minutes

As many readers will be aware, we introduced a cool new JSON datatype in 21c which offers more features and better performance for JSON related features.

Of course, whilst a new datatype is all well and good for 21c, many of us are not planning on moving to 21c because 19c is the long term support release, and so the intention will be to stay on 19c until the next long term support release comes out.

Alas for 19c, there is no such datatype.

But here’s a little tip for those of you that are running on Autonomous Database. Even if you are on 19c, then even though the entire suite of 21c JSON-related features are not available, you can still get the benefits of the new and improved JSON storage by simply modifying the DDL you use to create your JSON segments.

Note that this is only supported on Autonomous, and if you are on the Autonomous JSON database, this is automatically taken care of.

Happy JSON-ing!

4 responses to “Juice up your JSON on Autonomous”

Rajeshwaran Jeyabal

July 14, 2022 at 6:48 pm

No, not just Autonomous – even on 19c (19.14) running on Exacc platform supported.

demo@PDB19> create table t (x json);
create table t (x json)
*
ERROR at line 1:
ORA-00902: invalid datatype

demo@PDB19> create table t (x blob check(x is json format oson) );

Table created.

demo@PDB19> select banner_full from v$version;

BANNER_FULL
————————————————————————–
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production
Version 19.14.0.0.0

Reply
1. Connor McDonald
  
  July 15, 2022 at 6:55 am
  
  There is a HUGE difference between “runs” and “supported”. This facility is only supported on Autonomous.
  
  https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/overview-of-storage-and-management-of-JSON-data.html
  
  Reply
stewashton

July 21, 2022 at 12:02 am

Let’s say you have a column J19C in format OSON and I have a column J21C as a JSON data type. Once we both move to the *next* long term support release, will your DDL still work? Will both columns then benefit from the same full range of JSON capabilities?

Reply
Better JSON – from CLOB to BLOB – Learning is not a spectator sport

October 14, 2024 at 2:08 pm

[…] there is a better way. As I’ve covered in a previous post, if I am going to use BLOB format, I may as well take the opportunity to take advantage of the OSON […]

Reply