How to Convert Oracle Database Tables to JSON Format

How do you convert traditional relational data into JSON format using Oracle SQL?

JSON has become the de facto standard for data exchange in modern applications, and Oracle Database provides powerful built-in functions to transform relational data into JSON format directly within SQL queries.

This article explores various techniques to generate JSON output from Oracle tables, from basic object creation to complex hierarchical structures, complete with practical examples and best practices.

What are the Basic JSON Functions in Oracle SQL?

Oracle Database offers several JSON generation functions that transform relational data into JSON format.

The primary functions include JSON_OBJECT for creating JSON objects, JSON_ARRAY for creating JSON arrays, and aggregation functions like JSON_ARRAYAGG and JSON_OBJECTAGG for combining multiple rows into JSON structures.

These functions work seamlessly with standard SQL queries, allowing developers to generate JSON output without additional programming layers.

Starting with Oracle 12c Release 2, these functions became available, with enhancements in subsequent versions.

How to Create Simple JSON Objects from Oracle Tables?

Creating JSON objects from single rows is the foundation of JSON generation in Oracle SQL.

Let's start by creating a sample employee table and generating JSON objects from its data:

-- Create sample table
CREATE TABLE exjson_employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    salary NUMBER(10,2)
);

-- Insert sample data
INSERT INTO exjson_employees VALUES (101, 'John', 'Smith', 'john.smith@company.com', DATE '2025-01-15', 75000);
INSERT INTO exjson_employees VALUES (102, 'Sarah', 'Johnson', 'sarah.johnson@company.com', DATE '2025-02-20', 82000);
INSERT INTO exjson_employees VALUES (103, 'Michael', 'Brown', 'michael.brown@company.com', DATE '2025-03-10', 68000);
COMMIT;

-- Display the data
SELECT * FROM exjson_employees;

Query Result

Now let's generate JSON objects from this data using the JSON_OBJECT function, which creates a JSON object with key-value pairs from the specified columns:

SELECT JSON_OBJECT(
    'employeeId' VALUE employee_id,
    'firstName' VALUE first_name,
    'lastName' VALUE last_name,
    'email' VALUE email,
    'hireDate' VALUE hire_date,
    'salary' VALUE salary
) AS employee_json
FROM exjson_employees
WHERE employee_id = 101;

Query Result

The JSON_OBJECT function automatically handles data type conversions and creates properly formatted JSON strings.

Notice how the date is automatically converted to ISO 8601 format, which is the standard for JSON date representation.

How to Generate Nested JSON Structures?

Real-world applications often require nested JSON structures that represent relationships between entities.

Let's create a department table and demonstrate how to generate nested JSON:

-- Create department table
CREATE TABLE exjson_departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(100),
    location VARCHAR2(100)
);

-- Insert department data
INSERT INTO exjson_departments VALUES (10, 'Information Technology', 'New York');
INSERT INTO exjson_departments VALUES (20, 'Human Resources', 'Chicago');
INSERT INTO exjson_departments VALUES (30, 'Finance', 'Los Angeles');
COMMIT;

-- Add department_id to employees table
ALTER TABLE exjson_employees ADD department_id NUMBER;

-- Update employees with departments
UPDATE exjson_employees SET department_id = 10 WHERE employee_id IN (101, 103);
UPDATE exjson_employees SET department_id = 20 WHERE employee_id = 102;
COMMIT;

Now let's create a nested JSON structure that includes employee information with their department details embedded:

SELECT JSON_OBJECT(
    'employeeId' VALUE e.employee_id,
    'fullName' VALUE e.first_name || ' ' || e.last_name,
    'email' VALUE e.email,
    'department' VALUE JSON_OBJECT(
        'departmentId' VALUE d.department_id,
        'departmentName' VALUE d.department_name,
        'location' VALUE d.location
    )
) AS employee_with_dept
FROM exjson_employees e
JOIN exjson_departments d ON e.department_id = d.department_id
WHERE e.employee_id = 101;

Query Result

This example demonstrates how JSON_OBJECT functions can be nested to create hierarchical JSON structures.

The inner JSON_OBJECT creates the department object, which becomes a value within the outer employee object.

How to Create JSON Arrays from Multiple Rows?

JSON arrays are essential when you need to represent collections of data.

Oracle provides JSON_ARRAYAGG function to aggregate multiple rows into a JSON array.

Let's create a query that generates a JSON array of all employees in a specific department:

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'employeeId' VALUE employee_id,
        'name' VALUE first_name || ' ' || last_name,
        'email' VALUE email,
        'salary' VALUE salary
    )
    ORDER BY employee_id
) AS employees_array
FROM exjson_employees
WHERE department_id = 10;

Query Result

The JSON_ARRAYAGG function collects all the JSON objects created for each row and combines them into a single JSON array.

The ORDER BY clause within JSON_ARRAYAGG ensures the array elements are sorted by employee_id.

What are Advanced JSON Aggregation Techniques?

Oracle SQL provides advanced aggregation capabilities for creating complex JSON structures from relational data.

Let's explore JSON_OBJECTAGG and combining multiple aggregation functions:

-- Create a projects table
CREATE TABLE exjson_projects (
    project_id NUMBER PRIMARY KEY,
    project_name VARCHAR2(100),
    start_date DATE,
    budget NUMBER(12,2)
);

-- Create employee-project assignments
CREATE TABLE exjson_emp_projects (
    employee_id NUMBER,
    project_id NUMBER,
    role VARCHAR2(50),
    hours_allocated NUMBER
);

-- Insert project data
INSERT INTO exjson_projects VALUES (1001, 'Mobile App Development', DATE '2025-01-01', 250000);
INSERT INTO exjson_projects VALUES (1002, 'Data Analytics Platform', DATE '2025-02-01', 180000);
INSERT INTO exjson_projects VALUES (1003, 'Cloud Migration', DATE '2025-01-15', 320000);

-- Insert assignments
INSERT INTO exjson_emp_projects VALUES (101, 1001, 'Lead Developer', 160);
INSERT INTO exjson_emp_projects VALUES (101, 1003, 'Architect', 80);
INSERT INTO exjson_emp_projects VALUES (102, 1002, 'Project Manager', 120);
INSERT INTO exjson_emp_projects VALUES (103, 1001, 'Developer', 160);
INSERT INTO exjson_emp_projects VALUES (103, 1002, 'Analyst', 100);
COMMIT;

Now let's create a comprehensive JSON output showing departments with their employees and projects using JSON_OBJECTAGG, which creates a JSON object from key-value pairs across multiple rows:

SELECT JSON_OBJECT(
    'departmentId' VALUE d.department_id,
    'departmentName' VALUE d.department_name,
    'employeeCount' VALUE COUNT(DISTINCT e.employee_id),
    'totalSalary' VALUE SUM(e.salary),
    'employees' VALUE JSON_ARRAYAGG(
        JSON_OBJECT(
            'employeeId' VALUE e.employee_id,
            'name' VALUE e.first_name || ' ' || e.last_name,
            'projects' VALUE (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'projectName' VALUE p.project_name,
                        'role' VALUE ep.role,
                        'hoursAllocated' VALUE ep.hours_allocated
                    )
                )
                FROM exjson_emp_projects ep
                JOIN exjson_projects p ON ep.project_id = p.project_id
                WHERE ep.employee_id = e.employee_id
            )
        )
    )
) AS department_details
FROM exjson_departments d
JOIN exjson_employees e ON d.department_id = e.department_id
WHERE d.department_id = 10
GROUP BY d.department_id, d.department_name;

Query Result

This complex query demonstrates how multiple JSON functions can be combined to create deeply nested structures with aggregated data.

How to Handle Complex Hierarchical Data as JSON?

Hierarchical data, such as organizational structures or category trees, requires special handling when converting to JSON format.

Let's create an example with a self-referencing employee table to represent a management hierarchy:

-- Create organizational hierarchy table
CREATE TABLE exjson_org_hierarchy (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    position VARCHAR2(100),
    manager_id NUMBER,
    level_depth NUMBER
);

-- Insert hierarchical data
INSERT INTO exjson_org_hierarchy VALUES (1, 'Alice CEO', 'Chief Executive Officer', NULL, 1);
INSERT INTO exjson_org_hierarchy VALUES (2, 'Bob CTO', 'Chief Technology Officer', 1, 2);
INSERT INTO exjson_org_hierarchy VALUES (3, 'Carol CFO', 'Chief Financial Officer', 1, 2);
INSERT INTO exjson_org_hierarchy VALUES (4, 'David Dev Manager', 'Development Manager', 2, 3);
INSERT INTO exjson_org_hierarchy VALUES (5, 'Eve QA Manager', 'QA Manager', 2, 3);
INSERT INTO exjson_org_hierarchy VALUES (6, 'Frank Senior Dev', 'Senior Developer', 4, 4);
INSERT INTO exjson_org_hierarchy VALUES (7, 'Grace Junior Dev', 'Junior Developer', 4, 4);
INSERT INTO exjson_org_hierarchy VALUES (8, 'Henry QA Engineer', 'QA Engineer', 5, 4);
COMMIT;

-- Display the hierarchy
SELECT * FROM exjson_org_hierarchy ORDER BY level_depth, employee_id;

Query Result

Now let's create a recursive query to generate a hierarchical JSON structure representing the organization chart:

WITH org_tree (employee_id, employee_name, position, manager_id, employee_json, lvl) AS (
    -- Anchor member: start with CEO
    SELECT employee_id, employee_name, position, manager_id, 
           JSON_OBJECT(
               'employeeId' VALUE employee_id,
               'name' VALUE employee_name,
               'position' VALUE position,
               'subordinates' VALUE JSON_ARRAY()
           ),
           1
    FROM exjson_org_hierarchy
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member
    SELECT h.employee_id, h.employee_name, h.position, h.manager_id,
           JSON_OBJECT(
               'employeeId' VALUE h.employee_id,
               'name' VALUE h.employee_name,
               'position' VALUE h.position,
               'subordinates' VALUE (
                   SELECT JSON_ARRAYAGG(
                       JSON_OBJECT(
                           'employeeId' VALUE sub.employee_id,
                           'name' VALUE sub.employee_name,
                           'position' VALUE sub.position
                       )
                   )
                   FROM exjson_org_hierarchy sub
                   WHERE sub.manager_id = h.employee_id
               )
           ),
           ot.lvl + 1
    FROM exjson_org_hierarchy h
    JOIN org_tree ot ON h.manager_id = ot.employee_id
)
SELECT JSON_OBJECT(
    'organizationChart' VALUE JSON_ARRAYAGG(
        JSON_OBJECT(
            'employeeId' VALUE employee_id,
            'name' VALUE employee_name,
            'position' VALUE position,
            'directReports' VALUE (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'employeeId' VALUE e.employee_id,
                        'name' VALUE e.employee_name,
                        'position' VALUE e.position
                    )
                )
                FROM exjson_org_hierarchy e
                WHERE e.manager_id = org_tree.employee_id
            )
        )
    )
) AS org_chart_json
FROM org_tree
WHERE manager_id IS NULL;

For a simpler approach that works in all Oracle versions, let's create a query that shows each manager with their direct reports:

SELECT JSON_OBJECT(
    'managerId' VALUE m.employee_id,
    'managerName' VALUE m.employee_name,
    'position' VALUE m.position,
    'directReports' VALUE (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'employeeId' VALUE e.employee_id,
                'name' VALUE e.employee_name,
                'position' VALUE e.position
            )
            ORDER BY e.employee_name
        )
        FROM exjson_org_hierarchy e
        WHERE e.manager_id = m.employee_id
    )
) AS manager_with_team
FROM exjson_org_hierarchy m
WHERE EXISTS (
    SELECT 1 
    FROM exjson_org_hierarchy e 
    WHERE e.manager_id = m.employee_id
)
ORDER BY m.level_depth, m.employee_id;

Query Result

What are the Performance Considerations for JSON Generation?

Generating JSON from Oracle SQL requires careful consideration of performance implications, especially with large datasets.

JSON generation functions add processing overhead compared to standard SQL queries, so optimization becomes crucial for production systems.

Indexing Strategies

Creating appropriate indexes on columns used in JSON generation queries significantly improves performance.

Consider these indexing approaches:

-- Create indexes for JSON query optimization
CREATE INDEX idx_emp_dept ON exjson_employees(department_id);
CREATE INDEX idx_emp_projects_emp ON exjson_emp_projects(employee_id);
CREATE INDEX idx_emp_projects_proj ON exjson_emp_projects(project_id);

-- Create a composite index for complex queries
CREATE INDEX idx_emp_dept_salary ON exjson_employees(department_id, salary);

Query Optimization Techniques

Let's examine a performance comparison between different approaches to generate JSON data:

-- Create a performance test table with more data
CREATE TABLE exjson_perf_test (
    id NUMBER PRIMARY KEY,
    category VARCHAR2(50),
    value NUMBER,
    created_date DATE
);

-- Insert 10000 rows for testing
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO exjson_perf_test VALUES (
            i,
            'Category' || MOD(i, 10),
            ROUND(DBMS_RANDOM.VALUE(100, 1000), 2),
            DATE '2025-01-01' + MOD(i, 365)
        );
    END LOOP;
    COMMIT;
END;
/

-- Method 1: Single JSON aggregation (efficient)
SELECT JSON_OBJECT(
    'category' VALUE category,
    'totalValue' VALUE SUM(value),
    'recordCount' VALUE COUNT(*),
    'avgValue' VALUE ROUND(AVG(value), 2)
) AS category_summary
FROM exjson_perf_test
WHERE created_date >= DATE '2025-01-01'
  AND created_date < DATE '2025-02-01'
GROUP BY category;

Query Result (Sample)

Memory Management

JSON generation can consume significant memory, especially with large result sets.

Here are strategies to manage memory usage:

-- Solution 1: Use RETURNING CLOB to handle large JSON data
SELECT JSON_OBJECT(
    'page' VALUE 1,
    'pageSize' VALUE 100,
    'data' VALUE (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'id' VALUE id,
                'category' VALUE category,
                'value' VALUE value
            )
            RETURNING CLOB
        )
        FROM (
            SELECT id, category, value
            FROM exjson_perf_test
            ORDER BY id
            OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY
        )
    )
    RETURNING CLOB
) AS paginated_results
FROM dual;

Best Practices for Oracle JSON Generation

Following best practices ensures efficient and maintainable JSON generation from Oracle SQL queries.

Naming Conventions

Consistent naming conventions improve JSON readability and API compatibility:

-- Good practice: Use camelCase for JSON keys
SELECT JSON_OBJECT(
    'employeeId' VALUE employee_id,        -- camelCase
    'firstName' VALUE first_name,          -- camelCase
    'hireDate' VALUE hire_date,           -- camelCase
    'isActive' VALUE CASE 
        WHEN hire_date <= SYSDATE THEN 'true' 
        ELSE 'false' 
    END                                    -- boolean as string
) AS employee_json
FROM exjson_employees
WHERE ROWNUM = 1;

Query Result

Error Handling

Implement error handling for NULL values and data type conversions:

-- Handle NULL values appropriately
SELECT JSON_OBJECT(
    'employeeId' VALUE employee_id,
    'fullName' VALUE NVL(first_name || ' ' || last_name, 'Unknown'),
    'email' VALUE email ABSENT ON NULL,  -- Omit if NULL
    'phoneNumber' VALUE NULL              -- Explicitly include NULL
) AS employee_with_nulls
FROM exjson_employees;

Data Type Considerations

Oracle automatically converts data types to JSON-compatible formats, but understanding these conversions helps prevent issues:

-- Create a table with various data types
CREATE TABLE exjson_datatypes (
    id NUMBER,
    text_field VARCHAR2(100),
    number_field NUMBER(10,2),
    date_field DATE,
    timestamp_field TIMESTAMP,
    boolean_flag CHAR(1)
);

INSERT INTO exjson_datatypes VALUES (
    1,
    'Sample Text',
    1234.56,
    DATE '2025-03-15',
    TIMESTAMP '2025-03-15 14:30:45.123',
    'Y'
);

-- Demonstrate data type conversions
SELECT JSON_OBJECT(
    'id' VALUE id,
    'textField' VALUE text_field,
    'numberField' VALUE number_field,
    'dateField' VALUE date_field,
    'timestampField' VALUE timestamp_field,
    'booleanFlag' VALUE CASE boolean_flag 
        WHEN 'Y' THEN 'true' 
        ELSE 'false' 
    END
) AS datatype_demo
FROM exjson_datatypes;

Query Result

Conclusion

Oracle SQL provides comprehensive functionality for generating JSON output from relational data through functions like JSON_OBJECT, JSON_ARRAY, JSON_ARRAYAGG, and JSON_OBJECTAGG.

These functions enable developers to create simple objects, nested structures, arrays, and complex hierarchical JSON directly within SQL queries without requiring additional programming layers.

Performance optimization through proper indexing, query design, and memory management ensures efficient JSON generation even with large datasets.

Following best practices for naming conventions, error handling, and data type management results in maintainable and reliable JSON generation solutions.

The ability to transform relational data to JSON format within the database layer simplifies application architecture and improves performance by reducing data transfer and processing overhead.