JSON Type-Datatypes
New in 21c, Oracle introduces the JSON datatype. This datatype allows you to store JSON data natively in binary format in the database. It is an optimized binary storage that uses the OSON format. The OSON format is Oracle’s optimized binary JSON format. Using this format, query performance is greatly enhanced because textual JSON data is no longer parsed. The JSON datatype can be declared for a table, a materialized view, and PL/SQL parameters.
Here’s an example of how to create a table with a column defined with a JSON datatype:
$ sqlplus eoda/foo@PDB1
SQL> create table t (id number generated always as identity,json_data json,constraint t_pk primary key (id));
The VARCHAR2, CLOB and BLOB datatypes support JSON data. To illustrate this, I’ll use a bit of PL/SQL code that defines three variables, a VARCHAR2, a CLOB, and a BLOB. These variables will be populated with values and used to insert into the JSON datatype. For example:
declarel_varchar2 varchar2(32767);l_clob clob;l_blob blob;
beginl_varchar2 := ‘{“car”:”tesla”,”quantity”:100}’;l_clob := ‘{“car”:”chevy”,”quantity”:2}’;
l_blob := utl_raw.cast_to_raw(‘{“car”:”volvo”,”quantity”:10}’)insert into t (json_data) values (json(l_varchar2)); insert into t (json_data) values (json(l_clob)); insert into t (json_data) values (json(l_blob));end;/
You can retrieve JSON data in the same way you access other data in the database (including OCI and JDBC). The JSON data is stored in a binary format, so selecting the data without using a JSON function displays the raw data:
SQL> select * from t;ID JSON_DATA
1 7B22636172223A227465736C61222C227175616E74697479223A3130307D
2 7B22636172223A226368657679222C227175616E74697479223A327D
3 7B22636172223A22766F6C766F222C227175616E74697479223A31307D
Next, I’ll use the JSON_SERIALIZE function to convert the JSON data into a text format:
SQL> select id, json_serialize(json_data) as json_data from t;ID JSON_DATA
1 {“car”:”tesla”,”quantity”:100}
2 {“car”:”chevy”,”quantity”:2}
3 {“car”:”volvo”,”quantity”:10}
Other JSON functions can be used to retrieve and display text data. This example uses the JSON_VALUE function:
SQL> select t.id,json_value(t.json_data, ‘$.car’) car,
json_value(t.json_data, ‘$.quantity’ returning number) quantity from t;
In this manner, you can store and retrieve JSON data in its native binary format in the database. This allows for improved query performance when dealing with JSON data. This is because the data is not stored as text and therefore doesn’t need to be parsed.
Summary
In this chapter, we’ve examined many basic datatypes provided by Oracle; we’ve seen how they are physically stored and what options are available with each. We started with character strings, the most basic of types, and looked into considerations surrounding multibyte characters and raw binary data. We then discussed extended datatypes (available in Oracle 12c and above) and how this feature allows you to define VARCHAR2, NVARCHAR2, and RAW datatypes to be as large as 32,727 bytes. Next, we studied the numeric types, including the very precise Oracle NUMBER type and the floating-point types provided with Oracle.
We also gave consideration to the legacy LONG and LONG RAW types, concentrating on how you might work around their existence, as the functionality provided by these types falls far short of that provided by the LOB types. Next, we looked at the datatypes capable of storing dates and times. We covered the basics of date arithmetic, a perplexing issue until you’ve seen it demonstrated. Lastly, in the section on dates and timestamps, we looked at the INTERVAL type and how best to use it.
The most detailed part of the chapter from a physical storage perspective was the LOB section. The LOB type is frequently misunderstood by developers and DBAs alike, so the bulk of the section was spent looking at how they are physically implemented as well as certain performance considerations and the differences between SecureFiles and BasicFiles.
We also looked at the ROWID/UROWID type. For what now should be obvious reasons, you should not use this datatype as a database column, since ROWIDs are not immutable and no integrity constraints could enforce the parent/child relationship. Rather, you want to store primary keys if you need to point to another row.
The last datatype we examined was the JSON datatype. This new datatype was introduced in Oracle 21c. This allows you to store JSON data in a native binary format within the database. This improves query performance as you don’t have to parse the data when retrieving it (like you would if it was stored as text).