PL/SQL JSON Functions in Oracle Databases

Quick Answer: Which Oracle JSON Feature Should You Use?

Version-Aware Compatibility

A lot of confusion around Oracle JSON comes from mixing guidance for 19c and 21c+. The table below is the short version that matters most in practice.

Storing JSON Data Correctly

Storage choice is the first design decision. If you are on 21c+, the native JSON type is usually the right answer. If you are on 19c, use a textual or binary column and enforce validity with IS JSON.

-- Oracle 19c-style table design
CREATE TABLE orders_text (
    order_id   NUMBER PRIMARY KEY,
    order_doc  CLOB CHECK (order_doc IS JSON)
);

-- Oracle 21c+ table design
CREATE TABLE orders_native (
    order_id   NUMBER PRIMARY KEY,
    order_doc  JSON
);

-- If you only want JSON objects/arrays in a text column, tighten validation
-- with DISALLOW SCALARS instead of accepting any RFC 8259 scalar document.
-- Example:
-- CHECK (order_doc IS JSON DISALLOW SCALARS)

Creating JSON with SQL/JSON Functions

For most reporting and API-style queries, generate JSON in SQL instead of manually concatenating strings. Oracle's SQL/JSON generation functions are safer, easier to maintain, and version-stable.

• JSON_OBJECT: Build an object from name/value pairs.
• JSON_ARRAY: Build an array from literal or expression values.
• JSON_ARRAYAGG: Aggregate rows into a JSON array.
• JSON_OBJECTAGG: Aggregate key/value pairs into a JSON object.

SELECT JSON_OBJECT(
           'orderId'    VALUE o.order_id,
           'customerId' VALUE o.customer_id,
           'items'      VALUE JSON_ARRAYAGG(
                            JSON_OBJECT(
                              'sku' VALUE i.sku,
                              'qty' VALUE i.qty
                            )
                            ORDER BY i.line_no
                          )
           ABSENT ON NULL
           RETURNING CLOB
       ) AS order_json
FROM orders o
JOIN order_items i
  ON i.order_id = o.order_id
WHERE o.order_id = 1001
GROUP BY o.order_id, o.customer_id;

Querying JSON Data

Query functions are where most Oracle JSON work happens. The main rule is simple: use the function that matches the shape you expect back.

• JSON_VALUE: Return a scalar such as a string or number.
• JSON_QUERY: Return a JSON object or array.
• JSON_EXISTS: Test whether a path exists or a predicate matches.
• JSON_TABLE: Project JSON into rows and columns and then query it relationally.
• JSON_SERIALIZE: Convert JSON to display text, including pretty printed output.

-- Scalar extraction
SELECT order_id,
       JSON_VALUE(order_doc, '$.customerId' RETURNING NUMBER) AS customer_id
FROM orders_text;

-- Return a nested array as JSON text
SELECT order_id,
       JSON_QUERY(order_doc, '$.items' RETURNING CLOB PRETTY) AS items_json
FROM orders_text;

-- Relational projection of an array
SELECT o.order_id, jt.sku, jt.qty
FROM orders_text o
CROSS APPLY JSON_TABLE(
  o.order_doc,
  '$.items[*]'
  COLUMNS (
    sku VARCHAR2(30) PATH '$.sku',
    qty NUMBER       PATH '$.qty'
  )
) jt;

-- Pretty-print a full JSON document for inspection
SELECT JSON_SERIALIZE(order_doc PRETTY RETURNING CLOB)
FROM orders_native
WHERE order_id = 1001;

Updating Documents with JSON_TRANSFORM

When your database version includes JSON_TRANSFORM, use it for document updates instead of rebuilding the whole payload manually. Oracle documents the operations as ordered and atomic, which makes it a safer fit for multi-step edits.

UPDATE orders_native
SET order_doc = JSON_TRANSFORM(
                  order_doc,
                  SET '$.status' = 'shipped',
                  REMOVE '$.draftNote'
                )
WHERE order_id = 1001;

-- Preview the result without writing it back
SELECT JSON_TRANSFORM(
         order_doc,
         SET '$.status' = 'shipped',
         REMOVE '$.draftNote'
       ) AS updated_doc
FROM orders_native
WHERE order_id = 1001;

Working with JSON in PL/SQL

This is the part many articles skip. When the requirement is truly PL/SQL, Oracle's JSON object types are often more ergonomic than packing everything into one SQL statement.

• JSON_ELEMENT_T is the supertype for parsed JSON values.
• JSON_OBJECT_T represents a JSON object.
• JSON_ARRAY_T represents a JSON array.
• Getter methods such as get_string and get_number return typed values, while get() returns a JSON element you can inspect or cast.

DECLARE
  l_doc   JSON_OBJECT_T;
  l_items JSON_ARRAY_T;
BEGIN
  l_doc := JSON_OBJECT_T.parse(
    '{"orderId":1001,"status":"new","items":[{"sku":"A-1","qty":2}]}'
  );

  l_doc.put('status', 'validated');

  l_items := TREAT(l_doc.get('items') AS JSON_ARRAY_T);
  l_items.append(JSON_OBJECT_T.parse('{"sku":"B-9","qty":1}'));

  DBMS_OUTPUT.put_line(l_doc.to_string);
END;
/

Indexing and Performance Tips

Oracle JSON performance usually comes down to two things: not reparsing more than necessary and indexing the paths you actually filter on.

CREATE INDEX orders_customer_id_idx
    ON orders_text (
      JSON_VALUE(order_doc, '$.customerId' RETURNING NUMBER)
    );

-- Write the query with the same path and return type so Oracle can use the index.
SELECT order_id
FROM orders_text
WHERE JSON_VALUE(order_doc, '$.customerId' RETURNING NUMBER) = 42;

• If you filter on one or two stable scalar paths, a function-based index or virtual column is often the cleanest choice.
• If you need broader search across many paths, evaluate Oracle's JSON search index instead of stacking many narrow indexes.
• If you extract multiple fields from the same document in one query, JSON_TABLE is usually a better design than repeating several unrelated JSON_VALUE calls.
• On newer versions, native JSON storage reduces parsing overhead compared with repeatedly reading large textual JSON documents.

Common Pitfalls and Debugging Notes

• Silent nulls are dangerous. During development, prefer ERROR ON ERROR or explicit defaults instead of relying on forgiving behavior.
• Large JSON generation queries should usually specify RETURNING CLOB or, on native JSON systems, RETURNING JSON.
• If a query plan ignores your JSON index, check that the indexed expression and the predicate use the same path and compatible return type.
• If you are debugging payload shape, use JSON_SERIALIZE(... PRETTY) in SQL or your formatter tool outside the database to inspect the exact output.
• If the application only stores objects and arrays, do not leave that rule implicit. Enforce it with validation instead of letting scalar documents slip in by accident.

Conclusion

The best answer to "PL/SQL JSON functions in Oracle" is not one function name. It is a working split: generate and query JSON with SQL/JSON functions, use PL/SQL object types for procedural manipulation, and choose storage based on whether you are on 19c-style text JSON or 21c+ native JSON. Once you anchor the design to the right Oracle version, the rest of the function choices become much clearer.