Oracle JSON Duality Views

Create tables with data:

-- Customers table
CREATETABLE customers (
    customer_id NUMBER GENERATEDBYDEFAULTASIDENTITYPRIMARYKEY,
    name VARCHAR2(100)NOTNULL,
    email VARCHAR2(100)NOTNULL
);

-- Orders table
CREATETABLE orders (
    order_id NUMBER GENERATEDBYDEFAULTASIDENTITYPRIMARYKEY,
    customer_id NUMBERNOTNULLREFERENCES customers(customer_id),
    order_dateDATEDEFAULT SYSDATE,
    total_amount NUMBER(10,2)NOTNULL
);

• customer_id and order_id are auto-incremented.

• orders.customer_id is a foreign key referencing customers.

Step 2: Insert sample data

-- Insert sample customers
INSERTINTO customers (name, email)VALUES ('Alice Smith','alice@example.com');
INSERTINTO customers (name, email)VALUES ('Bob Johnson','bob@example.com');
INSERTINTO customers (name, email)VALUES ('Charlie Brown','charlie@example.com');

-- Insert sample orders
INSERTINTO orders (customer_id, order_date, total_amount)VALUES (1,DATE'2026-04-01',250);
INSERTINTO orders (customer_id, order_date, total_amount)VALUES (1,DATE'2026-04-03',150);
INSERTINTO orders (customer_id, order_date, total_amount)VALUES (2,DATE'2026-04-02',300);
INSERTINTO orders (customer_id, order_date, total_amount)VALUES (2,DATE'2026-04-05',120);
-- Charlie has no orders for variety

Step 3: Commit the changes

COMMIT;

• 3 customers: Alice, Bob, Charlie

• 4 orders (Alice 2, Bob 2, Charlie 0)

CREATE OR REPLACE VIEW customer_orders_json AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'order_id' VALUE o.order_id,
            'order_date' VALUE TO_CHAR(o.order_date,'YYYY-MM-DD'),
            'total_amount' VALUE o.total_amount
        )
        RETURNING CLOB
    ) AS orders_json
FROM
    customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.name, c.email;

SELECT customer_id, name, email, orders_json
FROM customer_orders_json
WHERE customer_id = 1;