13.9 JSON_TRANSFORM Operator KEEP

JSON_TRANSFORM operator KEEP removes all parts of the input data that are not targeted by at least one of the specified path expressions.

Operator KEEP is an exception, in that it can be immediately followed by more than one path expression. It is not an assignment operation (no = and RHS).

As at least one path expression is required, there is no way to use KEEP to specify keeping nothing. So you can't use KEEP to remove a topmost object or array.

Example 13-17 JSON_TRANSFORM: Keeping Only Certain Fields

Only fields a and d are kept in the json_transform result. 

SELECT json_transform('{"a":1, "b":2, "c":3, "d":4}',
                      KEEP '$.a', '$.d')

Result:

{"a:1", "d":4}

Example 13-18 JSON_TRANSFORM: Keeping Only Fields With Values Satisfying a Predicate

Only fields whose values are greater than 2 are kept in the result. Wildcard * specifies all fields, and predicate @>2 requires field values greater than 2. 

SELECT json_transform('{"a":1, "b":2, "c":3, "d":4}',
                      KEEP '$.*?(@ > 2)')

Result:

{"c:3", "d":4}

You can downscope the use of operation KEEP by using it within a NESTED PATH operation. Data outside the scope defined by the nested path is unaffected by the KEEP pruning. Example 13-21 illustrates this.

These are the handlers allowed for operator KEEP: IGNORE ON MISSING (default), ERROR ON MISSING.

Related Topics

Parent topic: Oracle SQL Function JSON_TRANSFORM