Working with JSON in Oracle APEX: Best Practices and Performance Optimization

JSON (JavaScript Object Notation) has become the de facto standard for lightweight data exchange across applications, especially within modern web-based platforms. For Oracle APEX developers, JSON plays a crucial role in interacting with RESTful services, persisting configuration settings, and seamlessly handling structured and semi-structured data within Oracle Database environments.

Oracle APEX applications often rely on JSON to bridge data flow between front-end interfaces and back-end Oracle databases, requiring robust mechanisms to parse, query, and manipulate JSON efficiently. However, the performance and scalability of JSON handling in APEX depend on the specific database version in use.

Oracle has continuously evolved its JSON processing capabilities, transitioning from APEX_JSON-based PL/SQL parsing to high-performance, native JSON functions embedded within SQL and PL/SQL.

Understanding the differences between these methods and adopting best practices can significantly enhance the efficiency, maintainability, and responsiveness of APEX applications. This article explores JSON handling options across different Oracle Database versions, compares the performance of APEX_JSON with native JSON functions, and provides recommendations for optimizing JSON processing in Oracle APEX environments.

JSON Handling Across Oracle Database Versions

Oracle Database 11.2 and 12.1.0.1

• No built-in JSON support in SQL or PL/SQL.
• APEX_JSON introduced in Oracle APEX 5.0 for JSON processing.
• Requires manual parsing in PL/SQL procedures.

• First introduction of JSON_TABLE, JSON_QUERY, and JSON_VALUE SQL functions.
• Allows direct JSON parsing within SQL queries.
• Reduces reliance on procedural PL/SQL code.

• Additional SQL functions for JSON generation and manipulation.
• Introduction of JSON_OBJECT_T and JSON_ARRAY_T in PL/SQL.
• Improved performance with native C-based JSON parsing.

Why Use Native JSON Processing Over APEX_JSON?

Many developers use APEX_JSON due to familiarity, but native JSON functions offer significant performance improvements.

Key Advantages of Native JSON Functions:

• Faster Execution: Native JSON parsing is implemented in C and optimized for high performance, whereas APEX_JSON is a PL/SQL-based implementation.
• SQL-Based Querying: JSON_TABLE enables direct querying of JSON data, making data manipulation simpler and more efficient.
• Better Scalability: Native JSON processing performs better under large datasets and high transaction loads.

Fetching JSON Data from External API

To demonstrate JSON handling, we retrieve earthquake data from the US Geological Survey (USGS) using APEX_WEB_SERVICE and store it in an Oracle table.

CREATE TABLE earthquake_json (
id NUMBER GENERATED ALWAYS AS IDENTITY,
fetched_at TIMESTAMP DEFAULT SYSTIMESTAMP,
document CLOB,
CONSTRAINT document_isjson CHECK (document IS JSON)
);

INSERT INTO earthquake_json(document)
VALUES (
apex_web_service.make_rest_request(
p_url => 'https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.geojson',
p_http_method => 'GET'
)
);

SELECT id, fetched_at, DBMS_LOB.getlength(document) FROM earthquake_json;

Parsing JSON: APEX_JSON vs Native JSON Functions

Using APEX_JSON (PL/SQL-based)
DECLARE
l_clob CLOB;
l_feature_count PLS_INTEGER;
l_time TIMESTAMP;
BEGIN
SELECT document INTO l_clob FROM earthquake_json WHERE id = 1;

l_time := SYSTIMESTAMP;
apex_json.parse(p_source => l_clob);

DBMS_OUTPUT.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - l_time)));
l_time := SYSTIMESTAMP;

Performance Results:

• Parsing Time: ~12 seconds.
• Array Count Retrieval: Nearly instantaneous.

Observations:

• The APEX_JSON.PARSE function builds an internal JSON representation in PL/SQL, which is costly.
• The get_count function is fast, as it operates on the pre-parsed JSON structure.

Using JSON_OBJECT_T (Native C-based)

DECLARE
l_clob CLOB;
l_json JSON_OBJECT_T;
l_features JSON_ARRAY_T;
l_time TIMESTAMP;
BEGIN
SELECT document INTO l_clob FROM earthquake_json WHERE id = 1;

l_time := SYSTIMESTAMP;
l_json := JSON_OBJECT_T.PARSE(l_clob);

DBMS_OUTPUT.put_line('Parsing Time: ' || EXTRACT(SECOND FROM (SYSTIMESTAMP - l_time)));
l_time := SYSTIMESTAMP;

Performance Results:

• Parsing Time: 0.12 seconds (100x faster than APEX_JSON).
• Array Count Retrieval: Still very fast.

Observations:

• Native JSON parsing significantly outperforms APEX_JSON.
• C-based string operations are highly optimized compared to PL/SQL implementations.

WITH eqdata AS (
SELECT e.id, e.title, e.mag
FROM earthquake_json j, JSON_TABLE(
document, '$.features[*]'
COLUMNS (
id VARCHAR2(20) PATH '$.id',
mag NUMBER PATH '$.properties.mag',
title VARCHAR2(200) PATH '$.properties.title'
)
) e
), minmax AS (
SELECT MIN(e.mag) minmag, MAX(e.mag) maxmag FROM eqdata e
)
SELECT e.id, e.title, e.mag
FROM eqdata e, minmax m
WHERE e.mag IN (m.minmag, m.maxmag);

Advantages of JSON_TABLE:

• Transforms JSON into a structured relational format.
• Highly efficient for querying and aggregations.
• Reduces need for procedural PL/SQL loops.

Best Practices for JSON Handling in Oracle APEX & SQL

1. Prefer Native JSON Processing over APEX_JSON for improved performance.
2. Use JSON_TABLE for Querying JSON Data instead of procedural loops.
3. Minimize Calls to APEX_JSON.PARSE—process JSON once and reuse parsed data.
4. Leverage Indexing and Partitioning to optimize JSON storage and retrieval.
5. Ensure Compatibility Across Database Versions by applying conditional compilation.

Upgrading to Oracle Database 12.2+ allows developers to fully leverage JSON_OBJECT_T, JSON_ARRAY_T, and JSON_TABLE, resulting in more efficient, scalable applications.

At Datavail, we are committed to helping businesses unlock the full potential of their Oracle APEX environments. Our team of experts ensures seamless JSON handling, enhanced database performance, and efficient integration of structured and unstructured data, allowing your applications to run optimally with minimal overhead.

Looking to enhance your Oracle APEX JSON processing and database performance? Contact Datavail’s specialists to guide you with tailored solutions to improve efficiency and scalability.