1. Generating JSON Payloads

When building microservices (e.g., exposing an Oracle Retail API via ORDS), you need to convert relational data into JSON. Forget string concatenation; Oracle provides native generation functions.

Creating JSON Objects and Arrays

• JSON_OBJECT(): Creates a key-value JSON object.
• JSON_ARRAY(): Creates a JSON array.
• JSON_ARRAYAGG(): Aggregates multiple relational rows into a single JSON array.

SELECT JSON_OBJECT(
         'department_id' VALUE d.department_id,
         'department_name' VALUE d.department_name,
         'employees' VALUE JSON_ARRAYAGG(
             JSON_OBJECT(
                 'emp_id' VALUE e.employee_id,
                 'name' VALUE e.first_name || ' ' || e.last_name
             )
         )
       ) AS dept_json
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

Output:

{
  "department_id": 10,
  "department_name": "Administration",
  "employees": [
    {"emp_id": 200, "name": "Jennifer Whalen"}
  ]
}

2. Shredding JSON with JSON_TABLE

If you receive a massive JSON payload from a REST endpoint and need to insert it into relational tables, JSON_TABLE is a lifesaver. It acts as a table function, projecting JSON elements into relational columns.

DECLARE
    v_payload CLOB := '
    {
      "order_id": 99887,
      "lines": [
        {"item": "SKU123", "qty": 5},
        {"item": "SKU456", "qty": 10}
      ]
    }';
BEGIN
    FOR r IN (
        SELECT jt.*
        FROM DUAL,
             JSON_TABLE(v_payload, '$'
                 COLUMNS (
                     order_id NUMBER PATH '$.order_id',
                     NESTED PATH '$.lines[*]' COLUMNS (
                         item_id VARCHAR2(20) PATH '$.item',
                         quantity NUMBER PATH '$.qty'
                     )
                 )
             ) jt
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Order: ' || r.order_id || ' Item: ' || r.item_id);
    END LOOP;
END;

How it works:

• '$' is the root context path.
• PATH '$.order_id' navigates down the JSON tree to find the value.
• NESTED PATH '$.lines[*]' iterates through the JSON array, creating a new relational row for every element in the array.

3. Storing and Validating JSON

Prior to Oracle 21c (which introduced the native JSON datatype), JSON was stored in CLOB or VARCHAR2 columns. To ensure bad data isn't inserted, you use the IS JSON check constraint.

CREATE TABLE integration_payloads (
    message_id NUMBER PRIMARY KEY,
    payload CLOB CONSTRAINT ensure_json CHECK (payload IS JSON)
);

Once the constraint is in place, you can query the data using simple dot notation:

SELECT p.payload.order_id, p.payload.customer.name
FROM integration_payloads p;