Oracle PL SQL Tutorial day 19 - working with JSON
Introduction:
With the increasing use of RESTful APIs and NoSQL-style data, JSON (JavaScript Object Notation) has become a dominant format for data exchange. Oracle Database, starting with version 12c, provides robust support for storing, querying, and processing JSON data directly from SQL and PL/SQL.
In this blog, we will explore how to:
- Parse JSON in PL/SQL
- Generate JSON from database tables
- Update JSON data
- Use PL/SQL collections with JSON
- Store JSON in table columns
Let’s dive in!
Section 1: Basic JSON Handling in Oracle
Oracle supports JSON natively using data types such as CLOB, VARCHAR2, or the JSON data type (from 21c+). JSON can be parsed using built-in packages like JSON_OBJECT_T, JSON_ARRAY_T, etc., part of the PL/SQL JSON API.
Example JSON:
{
"employee_id": 101,
"name": "Alice",
"department": "IT",
"skills": ["SQL", "PL/SQL", "JSON"]
}
Section 2: Creating and Parsing JSON in PL/SQL
Example: Parse a JSON object using JSON_OBJECT_T
DECLARE
v_json_text VARCHAR2(4000) := '{"employee_id":101,"name":"Alice","department":"IT"}';
v_json_obj JSON_OBJECT_T;
v_name VARCHAR2(100);
BEGIN
v_json_obj := JSON_OBJECT_T.parse(v_json_text);
v_name := v_json_obj.get_string('name');
DBMS_OUTPUT.put_line('Employee Name: ' || v_name);
END;
Output:
Employee Name: Alice
Section 3: Extracting Values from JSON Arrays
Suppose you have this JSON array:
{
"skills": ["SQL", "PL/SQL", "JSON"]
}
You can extract values like this:
DECLARE
v_json_text VARCHAR2(4000) := '{"skills":["SQL","PL/SQL","JSON"]}';
v_json_obj JSON_OBJECT_T;
v_array JSON_ARRAY_T;
i PLS_INTEGER;
BEGIN
v_json_obj := JSON_OBJECT_T.parse(v_json_text);
v_array := v_json_obj.get_array('skills');
FOR i IN 0 .. v_array.get_size - 1 LOOP
DBMS_OUTPUT.put_line('Skill: ' || v_array.get_string(i));
END LOOP;
END;
Output:
Skill: SQL
Skill: PL/SQL
Skill: JSON
Section 4: Generate JSON from Database Table
Oracle provides SQL functions to generate JSON from queries.
SELECT JSON_OBJECT(
'employee_id' VALUE e.emp_id,
'name' VALUE e.emp_name,
'department' VALUE e.dept
) AS employee_json
FROM employees e
WHERE emp_id = 101;
Output:
{"employee_id":101,"name":"Alice","department":"IT"}
Section 5: Storing JSON in Table Columns
Create a table with a column to store JSON:
CREATE TABLE employee_data (
id NUMBER GENERATED BY DEFAULT AS IDENTITY,
data CLOB CHECK (data IS JSON)
);
Insert JSON data:
INSERT INTO employee_data (data)
VALUES ('{"employee_id":102,"name":"Bob","department":"HR"}');
Section 6: Querying JSON Data from Tables
You can query specific fields from a JSON column using json_value():
SELECT
json_value(data, '$.name') AS employee_name,
json_value(data, '$.department') AS dept
FROM employee_data;
Output:
EMPLOYEE_NAME DEPT
-------------- ------
Bob HR
Section 7: Updating JSON Data in PL/SQL
You can modify values using the JSON PL/SQL object methods.
DECLARE
v_json_text VARCHAR2(4000) := '{"employee_id":102,"name":"Bob","department":"HR"}';
v_json_obj JSON_OBJECT_T;
BEGIN
v_json_obj := JSON_OBJECT_T.parse(v_json_text);
v_json_obj.put('department', 'Finance'); -- Update department
DBMS_OUTPUT.put_line(v_json_obj.to_clob());
END;
Output:
{"employee_id":102,"name":"Bob","department":"Finance"}
Section 8: JSON_TABLE – Powerful JSON Querying
JSON_TABLE can convert JSON into a relational format directly.
SELECT *
FROM JSON_TABLE(
'{"employee_id":101,"name":"Alice","department":"IT"}',
'$'
COLUMNS (
emp_id NUMBER PATH '$.employee_id',
emp_name VARCHAR2(50) PATH '$.name',
dept VARCHAR2(50) PATH '$.department'
)
);
Output:
EMP_ID EMP_NAME DEPT
------ ------
Conclusion
JSON use vastly with various technologies and oracle database support JSON. Data can be stored as JSON in oracle table. Data can be retrieve in normal format or JSON format from oracle database.
PL SQal also support to build JSON object and using Java inside oracle database, we can achieve a lot including REST API calls.
JSON support from oracle database version 12c. If oracle APEX installed then a lot of features are available for JSON and REST API.
0 Comments