Oracle JSON_TRANSFORM Function

The JSON_TRANSFORM function is Oracle's most powerful tool for modifying JSON data directly within a SQL query. While JSON_VALUE and JSON_QUERY are used to read data, JSON_TRANSFORM is used to change it.

This function allows you to perform a series of "operations"—like SET (update), REMOVE, INSERT, and REPLACE—on a JSON document and get the new, modified JSON as a result.

This is essential for:

• Updating a value in a JSON document (e.g., changing a status from "Active" to "Inactive").
• Removing sensitive information (e.g., removing a "ssn" field).
• Adding new fields or array elements.

You can use JSON_TRANSFORM in a SELECT statement to modify data on the fly, or in an UPDATE statement to permanently change JSON data stored in a table.

JSON_TRANSFORM Function Syntax

The syntax can be complex, as it supports many operations. The basic structure is a list of operations to perform in order.

JSON_TRANSFORM(
    json_expr,
    operation,
    [operation, ...]
    [ RETURNING data_type ]
)

[ RETURNING data_type ] )

The operation is where the real work happens. The most common operations are:

• SET 'path' = value: Updates the value at the specified path. If the path doesn't exist, it adds it (like an "upsert").
• REPLACE 'path' = value: Updates the value at the specified path. If the path doesn't exist, it does nothing.
• REMOVE 'path': Deletes the key-value pair at the specified path.
• INSERT 'path' = value: Inserts a new value. This is often used for arrays.
• APPEND 'path' = value: Adds a new element to the end of an array at the specified path.

Breakdown of the main syntax:

• json_expr: The column or string containing your JSON data.
• operation: The modification you want to make (e.g., SET '$.status' = 'Inactive'). You can list multiple operations, separated by commas.
• [RETURNING data_type] (Optional): Specifies the return type, such as VARCHAR2 (default), CLOB, or the native JSON type.

Oracle JSON_TRANSFORM Function Examples

All examples below use the DUAL table, which is a simple one-row table in Oracle for testing functions.

Example 1: Updating a Value with SET using JSON_TRANSFORM

This is the most common use case. We want to find a specific key in a JSON object and change its value. Here, we will change the status from "Active" to "Inactive".

Query:

SELECT
  JSON_TRANSFORM(
    '{"id": 123, "name": "John", "status": "Active"}',
    SET '$.status' = 'Inactive'
  ) AS "Modified_JSON"
FROM DUAL;

Result: (Notice the "status" field has been updated.)

Modified_JSON
-------------------------------------------------
{"id":123,"name":"John","status":"Inactive"}

Example 2: Removing a Field with REMOVE using JSON_TRANSFORM

This is a very common security/privacy use case. We have a JSON document, and we need to remove a sensitive field (like ssn) before returning it.

Query:

SELECT
  JSON_TRANSFORM(
    '{"user": "jdoe", "ssn": "999-99-9999", "level": "admin"}',
    REMOVE '$.ssn'
  ) AS "Redacted_JSON"
FROM DUAL;

Result: (The "ssn" key-value pair has been completely removed from the object.)

Redacted_JSON
-------------------------------------------
{"user":"jdoe","level":"admin"}