PL/SQL JSON Functions in Oracle Databases

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_info JSON
);

INSERT INTO products (product_id, product_info)
VALUES (101, '{"name": "Laptop", "brand": "TechCo", "specs": {"cpu": "i7", "ram": 16}}');

INSERT INTO products (product_id, product_info)
VALUES (102, '{"name": "Keyboard", "brand": "Accessory inc", "color": "Black"}');

-- Inserting invalid JSON will raise an error with the JSON datatype
-- INSERT INTO products (product_id, product_info)
-- VALUES (103, '{"name": "Mouse", "brand": "Logi"');

-- Creating a simple JSON object
SELECT JSON_OBJECT('name' VALUE 'Alice', 'age' VALUE 30) AS simple_json_object FROM dual;
-- Output: {"name":"Alice","age":30}

-- Creating a JSON object with a nested object
SELECT JSON_OBJECT('person' VALUE JSON_OBJECT('name' VALUE 'Bob', 'city' VALUE 'London')) AS nested_json_object FROM dual;
-- Output: {"person":{"name":"Bob","city":"London"}}

-- Creating a simple JSON array
SELECT JSON_ARRAY(1, 2, 3, 'apple', TRUE) AS simple_json_array FROM dual;
-- Output: [1,2,3,"apple",true]

-- Creating a JSON array of objects from table data (example table: employees)
-- Assume employees table has columns employee_id, name, job
/*
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    job VARCHAR2(100)
);
INSERT INTO employees VALUES (1, 'Alice', 'Engineer');
INSERT INTO employees VALUES (2, 'Bob', 'Manager');
INSERT INTO employees VALUES (3, 'Charlie', 'Analyst');
*/
SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
               'id'   VALUE employee_id,
               'name' VALUE name,
               'role' VALUE job
           ) ORDER BY employee_id
       ) AS employees_json_array FROM employees;
/*
Output (example):
[
  {"id":1,"name":"Alice","role":"Engineer"},
  {"id":2,"name":"Bob","role":"Manager"},
  {"id":3,"name":"Charlie","role":"Analyst"}
]
*/

-- Creating a JSON object where keys are employee IDs and values are employee names
SELECT JSON_OBJECTAGG(employee_id VALUE name) AS employees_json_object FROM employees;
/*
Output (example):
{"1":"Alice","2":"Bob","3":"Charlie"}
*/

-- Using the products table created earlier
-- Select product name using JSON_VALUE
SELECT product_id, JSON_VALUE(product_info, '$.name') AS product_name FROM products WHERE product_id = 101;
-- Output: 101, Laptop

-- Select the 'specs' object using JSON_QUERY
SELECT product_id, JSON_QUERY(product_info, '$.specs') AS product_specs FROM products WHERE product_id = 101;
-- Output: 101, {"cpu":"i7","ram":16}

-- Check if a product has a 'color' property using JSON_EXISTS
SELECT product_id, JSON_EXISTS(product_info, '$.color') AS has_color FROM products;
/*
Output:
101, FALSE
102, TRUE
*/

-- Extract a nested scalar value using JSON_VALUE
SELECT product_id, JSON_VALUE(product_info, '$.specs.cpu') AS cpu FROM products WHERE product_id = 101;
-- Output: 101, i7

-- Handle missing paths or invalid types with ON ERROR and ON EMPTY clauses (JSON_VALUE defaults: NULL ON ERROR, NULL ON EMPTY)
SELECT product_id,
       JSON_VALUE(product_info, '$.price' DEFAULT 'N/A' ON EMPTY) AS product_price, -- Path does not exist
       JSON_VALUE(product_info, '$.specs' ERROR ON ERROR) AS product_specs_value -- specs is an object, not scalar
FROM products WHERE product_id = 101;
-- Output: 101, N/A (for price), Error (for specs)

-- Using JSON_TABLE to extract multiple columns from JSON
SELECT p.product_id, jt.name, jt.brand, jt.cpu, jt.ram
FROM products p,
     JSON_TABLE(p.product_info, '$' -- Path to the root object
         COLUMNS (
             name  VARCHAR2(100) PATH '$.name',
             brand VARCHAR2(100) PATH '$.brand',
             -- Nested path for specs
             NESTED PATH '$.specs' -- Path to the nested object/array
                 COLUMNS (
                     cpu VARCHAR2(50) PATH '$.cpu',
                     ram NUMBER       PATH '$.ram'
                 )
         )
     ) jt;
/*
Output:
PRODUCT_ID, NAME    , BRAND          , CPU, RAM
----------------------------------------------------
101       , Laptop  , TechCo         , i7 , 16
102       , Keyboard, Accessory inc  ,    ,
*/

-- Using JSON_TABLE to flatten an array within JSON (example structure: {"order_id": 1, "items": [{"item_id": 10, "qty": 2}, {"item_id": 20, "qty": 1}]})
/*
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_details JSON
);
INSERT INTO orders VALUES (1, '{"order_id": 1, "items": [{"item_id": 10, "qty": 2}, {"item_id": 20, "qty": 1}]}');
INSERT INTO orders VALUES (2, '{"order_id": 2, "items": [{"item_id": 30, "qty": 5}]}');
*/
SELECT o.order_id, item.item_id, item.qty
FROM orders o,
     JSON_TABLE(o.order_details, '$.items[*]' -- Path to the array and '*' iterates elements
         COLUMNS (
             item_id NUMBER PATH '$.item_id',
             qty     NUMBER PATH '$.qty'
         )
     ) item;
/*
Output:
ORDER_ID, ITEM_ID, QTY
-----------------------
1       , 10     , 2
1       , 20     , 1
2       , 30     , 5
*/

-- Using the products table
-- Add a new field 'price'
SELECT JSON_TRANSFORM(product_info, SET '$.price' = 599.99) AS updated_json FROM products WHERE product_id = 101;
-- Output: {"name":"Laptop","brand":"TechCo","specs":{"cpu":"i7","ram":16},"price":599.99}

-- Update an existing field 'brand'
SELECT JSON_TRANSFORM(product_info, SET '$.brand' = 'Acme Corp') AS updated_json FROM products WHERE product_id = 102;
-- Output: {"name":"Keyboard","brand":"Acme Corp","color":"Black"}

-- Remove a field 'color'
SELECT JSON_TRANSFORM(product_info, REMOVE '$.color') AS updated_json FROM products WHERE product_id = 102;
-- Output: {"name":"Keyboard","brand":"Accessory inc"} -- Note: The example is SELECT, UPDATE would modify the table

-- Insert an element into an array (if specs.accessories is an array)
-- Let's assume product_info for 101 is '{"name":"Laptop","brand":"TechCo","specs":{"cpu":"i7","ram":16,"accessories":[]}}'
-- SELECT JSON_TRANSFORM(product_info, INSERT '$.specs.accessories[0]' = 'Mouse') AS updated_json FROM products WHERE product_id = 101;
-- Output: {"name":"Laptop","brand":"TechCo","specs":{"cpu":"i7","ram":16,"accessories":["Mouse"]}}

-- Multiple operations in one call
SELECT JSON_TRANSFORM(product_info,
                      SET    '$.price' = 649.50,
                      REMOVE '$.color'
       ) AS updated_json FROM products WHERE product_id = 102;
-- Output: {"name":"Keyboard","brand":"Accessory inc","price":649.5} -- Price added, color removed

-- Example UPDATE statement using JSON_TRANSFORM
-- UPDATE products
-- SET product_info = JSON_TRANSFORM(product_info, SET '$.price' = 649.50)
-- WHERE product_id = 101;

-- Using the products table

-- Create a JSON Search Index (indexes scalar values by default)
CREATE SEARCH INDEX products_json_search_idx ON products (product_info) FOR JSON;

-- Create a Function-Based Index on a specific path (e.g., product name)
CREATE INDEX products_name_fbi ON products (JSON_VALUE(product_info, '$.name'));

-- Create a Virtual Column and index it (alternative to FBI)
ALTER TABLE products ADD (product_name_vc VARCHAR2(100) GENERATED ALWAYS AS (JSON_VALUE(product_info, '$.name')));
CREATE INDEX products_name_vc_idx ON products (product_name_vc);

-- JSON Search Index for indexing specific paths or types (More advanced)
-- CREATE SEARCH INDEX products_json_complex_idx ON products (product_info) FOR JSON
-- PARAMETERS ('INCLUDE PATHS ($.name, $.brand, $.specs.cpu) DATAGUIDE ON');