Oracle SQL JSON operations

Complete summary of Oracle SQL JSON operations with detailed explanations for table creation, insertion of data, and various JSON-related operations:

---

1. Table Creation

To create a table that includes a column for storing JSON data:

CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(100),
emp_details CLOB CHECK (emp_details IS JSON)
);

emp_id: A unique identifier for each employee.

emp_name: Stores the name of the employee.

emp_details: A column to store JSON data, enforced by the CHECK (emp_details IS JSON) constraint to ensure valid JSON format.

---

1. Inserting Data

You can insert JSON data into the table like any other data, as long as the column is compatible with JSON (CLOB or JSON data type in Oracle 21c+):

INSERT INTO employees (emp_id, emp_name, emp_details)
VALUES (1, 'John Doe', '{"name": "John Doe", "department": "HR", "skills": ["Java", "SQL"]}');

The emp_details column contains a JSON string: {"name": "John Doe", "department": "HR", "skills": ["Java", "SQL"]}.

The JSON data includes key-value pairs for employee details, including an array of skills.

---

1. Retrieve JSON Value

Use JSON_VALUE to extract a specific scalar value (like a string, number, or boolean) from a JSON column:

SELECT JSON_VALUE(emp_details, '$.name') AS employee_name
FROM employees;

JSON_VALUE: Extracts the value of the name key from the JSON document stored in emp_details.

Result: Returns the name ("John Doe") from the JSON object.

---

1. Retrieve JSON Object or Array

Use JSON_QUERY to extract a complex value, like a nested JSON object or array:

SELECT JSON_QUERY(emp_details, '$.skills') AS employee_skills
FROM employees;

JSON_QUERY: Extracts the skills array from the emp_details JSON column.

Result: Returns the entire JSON array (["Java", "SQL"]) stored under the skills key.

---

1. Convert JSON to Relational Data

JSON_TABLE is used to convert JSON data into a relational format (rows and columns). It allows you to treat a JSON array like a table.

SELECT emp_id, jt.skill
FROM employees,
JSON_TABLE(
emp_details,
'$.skills[*]' -- Refers to the skills array
COLUMNS (skill VARCHAR2(50) PATH '$') -- Each skill will be a row
) jt;

JSON_TABLE: Treats the skills array in the JSON document as a set of rows.

Result: For each skill in the skills array, it returns a row for emp_id = 1:

emp_id skill

---

1 Java
1 SQL

---

1. Aggregate JSON Data

JSON_ARRAYAGG aggregates multiple rows into a single JSON array.

SELECT JSON_ARRAYAGG(emp_name) AS employee_names
FROM employees;

JSON_ARRAYAGG: Collects the emp_name values from all rows and combines them into a JSON array.

Result: If there are employees like John Doe and Jane Smith, the result is:

["John Doe", "Jane Smith"]

---

1. Construct a JSON Object

JSON_OBJECT is used to create a JSON object from column values.

SELECT JSON_OBJECT('id' VALUE emp_id, 'name' VALUE emp_name) AS employee_json
FROM employees;

JSON_OBJECT: Creates a JSON object with key-value pairs for id and name from the columns in the employees table.

Result: If emp_id = 1 and emp_name = 'John Doe', the result is:

{"id": 1, "name": "John Doe"}

---

1. Update JSON Data

Use JSON_MERGE_PATCH to modify specific parts of a JSON document (like updating an element or adding a new one).

UPDATE employees
SET emp_details = JSON_MERGE_PATCH(emp_details, '{"department": "Finance"}')
WHERE emp_id = 1;

JSON_MERGE_PATCH: Merges the new JSON object {"department": "Finance"} into the existing emp_details JSON document. If the key department already exists, its value will be updated.

Result: After the update, the emp_details for emp_id = 1 will be:

{"name": "John Doe", "department": "Finance", "skills": ["Java", "SQL"]}

---

1. Validate JSON

IS JSON is used to ensure that a column contains valid JSON data.

SELECT emp_details
FROM employees
WHERE emp_details IS JSON;

IS JSON: Ensures that the emp_details column contains valid JSON data.

Result: Only rows where the emp_details column is valid JSON will be returned.

---

1. Filter Data Based on JSON Content

JSON_EXISTS checks if a specific path exists within a JSON document.

SELECT emp_name
FROM employees
WHERE JSON_EXISTS(emp_details, '$.skills[?(@ == "Java")]');

JSON_EXISTS: Filters rows where the skills array contains "Java".

Result: If the emp_details JSON contains "Java" in the skills array, the employee will be returned.

---

Summary of Key Functions:

JSON_VALUE: Extracts scalar values (string, number, etc.) from JSON.

JSON_QUERY: Extracts complex JSON structures (arrays or objects) from JSON.

JSON_TABLE: Converts JSON into a relational format (rows and columns).

JSON_ARRAYAGG: Aggregates values into a JSON array.

JSON_OBJECT: Creates JSON objects from column values.

JSON_MERGE_PATCH: Modifies JSON data by merging or updating.

JSON_EXISTS: Checks if a JSON path exists.

---

These are the key operations for working with JSON data in Oracle SQL, enabling the storage, retrieval, transformation, and manipulation of JSON documents within relational databases.