JSON_TRANSFORM

Syntax

Description of the illustration json_transform.eps

JSON_TRANSFORM_returning_clause::=

Description of the illustration json_transform_returning_clause.gif

JSON_passing_clause::=

For details on JSON_passing_clause see JSON_EXISTS Condition.

operation ::=

Description of the illustration operation.eps

removeOp ::=

Description of the illustration remove_op.eps

insertOp ::=

Description of the illustration insert_op.eps

replaceOp ::=

Description of the illustration replace_op.eps

rhsExpr ::=

Description of the illustration rhs_expr.eps

appendOp ::=

Description of the illustration append_op.eps

setOp ::=

Description of the illustration set_op.eps

renameOp ::=

Description of the illustration rename_op.eps

keepOp ::=

Description of the illustration keep_op.eps

Purpose

You can use the JSON_TRANSFORM function to change input JSON data (or pieces of JSON data), by specifying one or more modifying operations that perform changes to the JSON data. You will get the modified JSON as output.

The input to the function is a SQL expression that contains a JSON value. For example, the input can be a JSON type instance, a VARCHAR2 column with or without an IS JSON check constraint, or a function call returning JSON data.

Examples

Example 1 : Update a JSON Column with a Timestamp

UPDATE t SET jcol = JSON_TRANSFORM(jcol, SET '$.lastUpdated' = SYSTIMESTAMP)

Example 2 : Remove a Social Security Number before Shipping JSON to a Client

SELECT JSON_TRANSFORM (jcol, REMOVE '$.ssn') FROM t WHERE …

JSON_TRANSFORM_returning_clause

If the input data is JSON, then the output data type is also JSON. For all other input types, the default output data type is VARCHAR2(4000).

For a fuller discussion of JSON_TRANSFORM with examples, see Oracle SQL Function JSON_TRANSFORM.