JSON Functions in PL/SQL

JSON (JavaScript Object Notation) has become more popular in modern applications, and databases like Oracle support JSON data. JSON is widely used in modern applications for its simplicity and flexibility, making it a popular format for exchanging data between systems.

In this article, we will explain the JSON functions in PL/SQL, their basic structure, and examples. Also show how to work easily and effectively with JSON data in Oracle databases and conversion of JSON data to relational data.

JSON Functions in PL/SQL

PL/SQL offers built-in JSON functions to work with JSON data. These functions help store, query, and modify JSON easily. These functions help create, extract, update, and validate JSON data with ease. which allows developers to perform complex operations on JSON fields important for developers handling dynamic or semi-structured data.

Key Features of JSON Functions in Oracle

• JSON Data Handling: Simplifies the storage, querying, and validation of JSON data within Oracle databases.
  
• Seamless Integration: Allows developers to handle semi-structured JSON data alongside traditional relational data, making it easy to work with modern web applications.
  
• Performance: Oracle optimizes queries involving JSON data for performance, especially with indexing.

Commonly Used JSON Functions in PL/SQL

Here are the most commonly used JSON functions in Oracle PL/SQL, which enable us to create, extract, and manipulate JSON data:

JSON_OBJECT

It Creates a JSON object from the specified key-value pairs.

SELECT JSON_OBJECT('key' VALUE expression) AS json_output FROM dual;

JSON_ARRAY

Creates a JSON array from the given set of values.

SELECT JSON_ARRAY(value1, value2, ...) AS json_array FROM dual;

JSON_VALUE

Extracts a scalar value from a JSON document.

SELECT JSON_VALUE(json_column, 'json_path') AS value FROM table_name;

JSON_QUERY

Extracts an object or an array from a JSON document.

SELECT JSON_QUERY(json_column, 'json_path') AS json_output FROM table_name;

JSON_TABLE

Converts JSON data to relational data (rows and columns).

SELECT * FROM JSON_TABLE(    json_column,    '$.json_path'    COLUMNS (        column_name1 PATH 'path1',        column_name2 PATH 'path2'    )) jt;

Example 1: Using JSON_OBJECT and JSON_ARRAY

In this example, we will demonstrate how to create a JSON object and a JSON array in PL/SQL using the JSON_OBJECT and JSON_ARRAY functions.

Query:

SELECT 
    JSON_OBJECT('id' VALUE 101, 'name' VALUE 'John Doe') AS json_obj,
    JSON_ARRAY(10, 20, 30) AS json_array 
FROM dual;

Output:

Explanation:

• The JSON_OBJECT function creates a JSON object with two key-value pairs: "id": 101 and "name": "John Doe".
  
• The JSON_ARRAY function creates an array with three values: 10, 20, 30.

Example 2: Extracting Values with JSON_VALUE

Let’s say we have a table called Employees that stores employee information in JSON format. Suppose We want to extract a specific employee’s name from the JSON data using the JSON_VALUE function.

Query:

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_data CLOB
);

INSERT INTO Employees (emp_id, emp_data) 
VALUES (1, '{"name": "Alice", "age": 30, "department": "HR"}');

SELECT JSON_VALUE(emp_data, '$.name') AS emp_name
FROM Employees
WHERE emp_id = 1;

Output:

Explanation:

• The JSON_VALUE function is used to get the value of the name key from the emp_data JSON column for the employee whose emp_id is 1.

Example 3: Using JSON_TABLE to Convert JSON to Relational Data

The JSON_TABLE function allows you to convert JSON data into relational data. In this example, we will extract employee information from a JSON string and present it in a tabular format.

Query:

SELECT *
FROM JSON_TABLE(
    '{"employees": [{"name": "John", "age": 35}, {"name": "Jane", "age": 29}]}',
    '$.employees[*]'
    COLUMNS (
        emp_name VARCHAR2(20) PATH '$.name',
        emp_age NUMBER PATH '$.age'
    )
) jt;

Output:

Explanation:

• The JSON_TABLE function extracts each employee’s name and age from the JSON document and presents the data in a relational format (rows and columns).

Example 4: Validating JSON Data with IS JSON Function

We can use the IS JSON function to check whether a field contains valid JSON data. This function is useful when we need to validate input data in a column.

Query:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_info CLOB
);

INSERT INTO Products (product_id, product_info) 
VALUES (1, '{"product_name": "Laptop", "price": 1200}');

SELECT product_info
FROM Products
WHERE product_info IS JSON;

Output:

Explanation:

• The IS JSON function confirms whether the product_info column contains a valid JSON object. In this case, the query returns the product information for rows where the JSON structure is correct.

Conclusion

In this article, we explained the various JSON functions in PL/SQL that help in working with JSON data stored in Oracle databases. These functions simplify the process of creating, extracting, transforming, and validating JSON data within our PL/SQL programs.

With JSON becoming increasingly popular, understanding these functions is crucial for any developer working with modern applications and semi-structured data. The IS JSON function is useful for validating JSON fields, ensuring the data integrity.