13.7 JSON_TRANSFORM Operator INSERT
JSON_TRANSFORM operator INSERT inserts a
value at a given location (an object field or an array position).
It inserts the value of an RHS SQL expression at the location that's targeted by the LHS path expression. That path expression must target either a field of an object or an array position (otherwise, an error is raised). By default, an error is raised if a targeted object field already exists.
(INSERT for an object field has the effect of
SET with clause CREATE ON MISSING (default for
SET), except that the default behavior for ON
EXISTING is ERROR, not REPLACE.)
You can specify an array position that is past the current end of an
array. In that case, the array is lengthened to accommodate insertion of the
value at the indicated position, and the intervening positions are filled with JSON
null values.
For example, if the input JSON data is {"a":["b"]} then
INSERT '$.a[3]'=42 returns {"a":["b", null,
null 42]} as the modified data. The elements at array positions
1 and 2 are null.
Example 13-13 JSON_TRANSFORM: Inserting a Field Into an Object
This code inserts member "a":"hello" into the input object, which is
empty.
SELECT json_transform('{}',
INSERT '$.a' = 'hello')Result:
{"a":"hello"}Example 13-14 JSON_TRANSFORM: Failing to Insert a Field That Already Exists
This code tries to insert member "a":"hello" into the input object.
The operation fails because the input object already has a field
a.
SELECT json_transform('{"a":1}',
INSERT '$.a' = 'hello')This error is raised: ORA-40763: value already exists.
Example 13-15 JSON_TRANSFORM: Inserting an Element Into an Array
This code inserts string "hello" as the (new) third element of array
a. The original third (3) and fourth
(4) elements become the fourth and fifth elements.
SELECT json_transform('{"a":[1,2,3,4]}',
INSERT '$.a[2]' = 'hello')Result:
{"a":[ 1, 2, "hello", 3, 4 ]}These are the handlers allowed for operator INSERT:
-
ERROR ON EXISTING(default),IGNORE ON EXISTING,REPLACE ON EXISTING. -
CREATE ON MISSING(default). -
NULL ON NULL(default),IGNORE ON NULL,ERROR ON NULL,REMOVE ON NULL. -
NULL ON EMPTY(default),IGNORE ON EMPTY,ERROR ON EMPTY. -
ERROR ON ERROR(default),IGNORE ON ERROR.