Introduction to Oracle JSON Data Type

Oracle 12c introduced support for the JSON data type, which can store and manipulate data in JSON format. JSON is a lightweight data interchange format that has become a standard for internet data exchange.

Syntax

In Oracle, JSON data type can be stored using VARCHAR2 or CLOB data type. The IS JSON clause can be used to validate if a value is in JSON format, for example:

SELECT '{"name": "Alice", "age": 30}' IS JSON FROM DUAL;

The result will return 1 indicating it is in JSON format, and 0 if it is not.

Use Cases

The JSON data type can be used to store structured data such as configuration files, API responses, and more. Additionally, JSON can also be used to store semi-structured data such as log information, events, and more. In some scenarios, using JSON can avoid excessive table design, making the application more flexible.

Examples

Example 1: Creating a column with JSON data type

VARCHAR2 or CLOB data types can be used to create a column with JSON data type, for example:

CREATE TABLE employees (
  id NUMBER,
  name VARCHAR2(100),
  salary NUMBER,
  attributes CLOB CHECK (attributes IS JSON)
);

Example 2: Querying JSON data using JSON_VALUE function

The JSON_VALUE function can be used to query JSON data, for example:

SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name') FROM DUAL;

The result will return Alice.

Conclusion

The JSON data type can be used to store structured data, avoiding excessive table design and making the application more flexible. Additionally, Oracle provides many functions to manipulate JSON data, such as JSON_VALUE, JSON_QUERY, and more, making it convenient to query, modify, and transform JSON data.