Extract key/value from JSON records in Oracle 19c

by

Summary

The issue describes a performance bottleneck when extracting key/value pairs from JSON documents stored in an Oracle 19c database, involving over 10 million rows. The current approach uses JSON_KEY_LIST and procedural iteration, which is inefficient for large-scale extraction. The core challenge is handling dynamic JSON structures where each record contains a variable number of key/value pairs without hardcoding paths. JSON_TABLE is indeed the correct SQL-based solution for this scenario, allowing set-based processing of JSON arrays or objects. This postmortem analyzes why the procedural method fails at scale and how to implement a robust, performant extraction mechanism.

Root Cause

The root cause of the performance degradation is the use of a procedural, row-by-row processing model for JSON data extraction. Oracle’s JSON_KEY_LIST combined with procedural loops forces the database to context-switch between SQL and PL/SQL for every row and every key within each row.

  • Inefficient context switching: For 10M+ rows with potentially dozens of keys each, this results in billions of procedural invocations, leading to high CPU and memory overhead.
  • Lack of set-based optimization: SQL engines optimize for set operations. Procedural extraction prevents the optimizer from using efficient plans like parallel query, partition pruning, or index usage on the extracted data.
  • Scalability limitation: As data volume grows, the linear increase in processing time becomes unsustainable. The procedural method does not leverage Oracle’s native JSON indexing capabilities (e.g., JSON Value Indexing).
  • Dynamic structure handling: The inability to hardcode paths is correctly identified, but the response (using loops) addresses it at the cost of performance, rather than using a dynamic SQL or set-based approach.

Why This Happens in Real Systems

In real-world systems, this pattern emerges from several common scenarios:

  • Legacy or ad-hoc development: Often, initial development starts with small datasets where procedural extraction is acceptable. As the system scales, these patterns persist without refactoring.
  • Misunderstanding of Oracle’s JSON features: Developers may not be aware of JSON_TABLE (introduced in Oracle 12c and enhanced in 19c) or assume that dynamic JSON requires procedural logic.
  • Complexity avoidance: Writing dynamic SQL or using JSON_TABLE with variable paths can seem complex, leading to the choice of a simpler, albeit slower, procedural method.
  • Performance not caught in testing: With smaller test datasets, the performance issue may not manifest, allowing it to propagate to production. The 10M+ row volume is a typical scale where such issues become critical.

Real-World Impact

The impact of inefficient JSON extraction at scale is severe in production environments:

  • High database resource consumption: Procedural loops can spike CPU usage, leading to contention and slower response times for other queries on the same instance.
  • Increased runtime and latency: Processing 10M+ rows can take hours or days, delaying ETL jobs, reporting, or application features that rely on this data.
  • Scalability bottlenecks: As data grows, the system may hit performance walls, requiring hardware upgrades or architectural changes, increasing costs.
  • Risk of timeouts and failures: Long-running processes may be killed by resource managers or fail due to memory exhaustion, leading to data inconsistency and operational issues.

Example or Code

For demonstration, assume a table JSON_DATA with columns ID (number) and JSON_DOC (clob) storing the JSON strings. The goal is to extract all key/value pairs into a relational format.

-- Create sample table and data (for illustration only)
CREATE TABLE JSON_DATA (
    ID NUMBER,
    JSON_DOC CLOB
);

INSERT INTO JSON_DATA VALUES (1, '{"KEY1":"VAL1","KEY2":"VAL2","KEY3":"VAL3"}');
INSERT INTO JSON_DATA VALUES (2, '{"KEY2":"VAL2","KEY8":"VAL8","KEY5":"VAL5"}');

-- Using JSON_TABLE for set-based extraction (the recommended solution)
-- This dynamically processes all key/value pairs without hardcoding paths
SELECT 
    j.ID,
    jt.KEY_NAME AS "Column1",
    jt.KEY_VALUE AS "Column2"
FROM 
    JSON_DATA j,
    JSON_TABLE(
        j.JSON_DOC,
        '$' -- Process the entire JSON object
        COLUMNS (
            NESTED PATH '$.*' -- Dynamically iterates over all key/value pairs
            COLUMNS (
                KEY_NAME VARCHAR2(100) PATH '$[0]', -- In Oracle 19c, for key/value objects, use a different approach
                KEY_VALUE VARCHAR2(100) PATH '$[1]'
            )
        )
    ) AS jt
WHERE j.ID IS NOT NULL;
-- Note: The above JSON_TABLE syntax assumes an array. For dynamic objects, use:
-- NESTED PATH '$' COLUMNS (KEY_NAME VARCHAR2(100) PATH 'key()', KEY_VALUE VARCHAR2(100) PATH 'value()')
-- However, in Oracle 19c, for strict key/value extraction, a common method is:

SELECT 
    j.ID,
    jt.KEY_NAME AS "Column1",
    jt.KEY_VALUE AS "Column2"
FROM 
    JSON_DATA j,
    JSON_TABLE(
        j.JSON_DOC,
        '$' 
        COLUMNS (
            NESTED PATH '$.*' 
            COLUMNS (
                KEY_NAME VARCHAR2(100) PATH 'key()',
                KEY_VALUE VARCHAR2(100) PATH 'value()'
            )
        )
    ) AS jt;

How Senior Engineers Fix It

Senior engineers address this by moving from procedural to set-based processing and optimizing the database environment. Key takeaways include leveraging Oracle’s native JSON functions and ensuring proper indexing.

  • Adopt JSON_TABLE for extraction: Use JSON_TABLE in a SQL query to convert JSON objects into relational rows dynamically. This allows the optimizer to use parallel execution and reduces context switching.
  • Implement JSON indexing: Create a search index on the JSON column for efficient filtering: CREATE INDEX json_idx ON JSON_DATA (JSON_DOC) INDEXTYPE IS CTXSYS.JSON_SEARCH_INDEX;. For key/value extraction, consider function-based indexes on extracted values if specific keys are queried often.
  • Use bulk processing in PL/SQL (if SQL alone isn’t feasible): For very complex transformations, combine JSON_TABLE with bulk collect in PL/SQL to minimize context switches, but prefer pure SQL when possible.
  • Partition large tables: Partition the JSON_DATA table by key ranges or dates if applicable, enabling partition pruning during extraction.
  • Test with realistic data volumes: Benchmark with 10M+ rows to validate performance, ensuring the solution scales linearly.

Why Juniors Miss It

Junior engineers often miss these optimizations due to gaps in experience with advanced database features and scalability patterns. Common pitfalls include over-reliance on familiar procedural code and underestimating the impact of set-based operations.

  • Limited knowledge of JSON features: Juniors may not be trained on JSON_TABLE or dynamic path extraction, defaulting to loops with JSON_KEY_LIST which they learned from basic tutorials.
  • Focus on functionality over performance: Initial development often prioritizes getting results correct over efficient execution, especially if dataset sizes are unknown or small.
  • Assumption that dynamic data requires procedural code: There’s a misconception that variable JSON structures can’t be handled in pure SQL, leading to avoidable complexity.
  • Inadequate testing for scale: Without stress testing on large volumes, performance issues remain hidden until production, where they become critical. Mentors should emphasize performance testing early in the development cycle.