Efficient Methods for Converting SQL Query Results to JSON in Oracle 12c

Evolution of JSON Generation Technology in Oracle Database

With the proliferation of web applications and microservices architecture, JSON has become the de facto standard for data exchange. Prior to Oracle 12c, developers typically converted query results to JSON at the application layer (e.g., PHP, Java), which presented significant performance bottlenecks. In a typical scenario: fetching thousands of rows from an Oracle database and converting them using json_encode() in PHP could require over 5 seconds of additional processing time, with most consumed by data serialization and character encoding conversion.

Native JSON Support in Oracle 12c

Oracle 12c Release 2 (12.2) introduced powerful native JSON capabilities, allowing direct JSON document generation at the SQL layer, fundamentally transforming traditional data processing workflows. Core features include the JSON_OBJECT and JSON_ARRAY functions, which seamlessly convert relational data into JSON format.

Basic JSON Generation Examples

Consider a simple employee table query scenario. Using the JSON_OBJECT function creates JSON objects with custom key names:

SELECT JSON_OBJECT('ID' IS EMPLOYEE_ID, 'FirstName' IS FIRST_NAME, 'LastName' IS LAST_NAME) FROM HR.EMPLOYEES;

The execution generates a sequence of independent JSON objects:

{"ID":100,"FirstName":"Steven","LastName":"King"}
{"ID":101,"FirstName":"Neena","LastName":"Kochhar"}

For array format, the JSON_ARRAY function can be used:

SELECT JSON_ARRAY(EMPLOYEE_ID, FIRST_NAME, LAST_NAME) FROM HR.EMPLOYEES;

This produces:

[100,"Steven","King"]
[101,"Neena","Kochhar"]

Converting Complete Result Sets to JSON Arrays

For scenarios requiring the entire query result as a single JSON array, combine with the JSON_ARRAYAGG aggregate function:

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        KEY 'col1' VALUE col1,
        KEY 'col2' VALUE col2
    ) FORMAT JSON RETURNING CLOB
) AS json_doc
FROM tab;

This query generates CLOB data with the following structure:

[{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"}]

Character Encoding and Unicode Handling

In practical applications, character encoding processing is crucial for JSON generation. Oracle's JSON functions automatically handle Unicode escaping, ensuring data correctness across different character set environments. For example, when the database uses AL32UTF8 character set and the client requires ISO-8859-2, JSON functions automatically convert non-ASCII characters to Unicode escape sequences (e.g., \uXXXX format).

Developers can specify output data type and character set using the RETURNING clause:

SELECT JSON_OBJECT('name' IS employee_name RETURNING VARCHAR2(4000) CHARACTER SET WE8ISO8859P2) FROM employees;

Performance Optimization and Large-Scale Data Processing

Native JSON generation offers significant performance advantages. Test data shows that generating 5.6MB of JSON documents takes approximately 1 second, providing several-fold performance improvement compared to application-layer conversion solutions. This primarily results from:

1. Direct data serialization within the database, reducing network transmission overhead
2. Avoiding memory copying for intermediate format conversions
3. Leveraging Oracle's optimizer for query plan optimization

For extremely large datasets, a paging strategy is recommended:

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(*)
    RETURNING CLOB
) AS json_chunk
FROM (
    SELECT * FROM large_table
    ORDER BY id
    OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY
);

Oracle Version Feature Comparison

Oracle 12c Release 1 (12.1.0.2)

This version first introduced JSON support but with relatively limited functionality. Developers can use third-party packages like PL/JSON as supplementary solutions:

DECLARE
  ret json;
BEGIN
  ret := json_dyn.executeObject('SELECT * FROM tab');
  ret.print();
END;

Syntax Simplification in Oracle 19c

Oracle 19c further simplifies JSON generation syntax, supporting wildcard operations:

SELECT JSON_OBJECT(*) FROM hr.employees;

This syntax automatically converts all table columns into key-value pairs of JSON objects, greatly improving development efficiency.

Alternative Approaches and Compatibility Considerations

For earlier Oracle versions (e.g., 9i, 10g, 11g), JSON generation can be indirectly achieved through XML conversion:

DECLARE
  l_xml  XMLTYPE;
  l_json XMLTYPE;
BEGIN
  l_xml := itstar_xml_util.sql2xml('SELECT empno, ename, job FROM emp');
  l_json := itstar_xml_util.xml2json(l_xml);
  DBMS_OUTPUT.PUT_LINE(l_json.getClobVal());
END;

This method typically generates JSON containing root elements (e.g., ROWSET), requiring appropriate processing on the client side.

Best Practice Recommendations

1. Version Selection: Prefer Oracle 12c R2 or later versions for optimal performance and feature support
2. Data Type Optimization: Select appropriate return types (VARCHAR2, CLOB, or BLOB) based on data volume
3. Character Set Consistency: Ensure consistent character set configuration across database, JSON generation, and client applications
4. Error Handling: Use ON ERROR clauses to handle exceptions during conversion
5. Index Optimization: Create appropriate indexes for columns involved in JSON generation queries

By effectively utilizing Oracle's native JSON capabilities, developers can significantly improve data exchange efficiency, reduce application layer burden, and ensure data integrity and consistency. As Oracle Database continues to evolve, JSON processing capabilities will be further enhanced, providing stronger data support for modern application architectures.