Inserts a JSON value into a JSON array or object at a specified position.
Syntax
JSON json_insert(JSON <input>, ARRAY STRING <path>, JSON <value>, BOOLEAN <insert_after>);Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
input |
Yes | JSON array or JSON object | The JSON document to insert into. |
path |
Yes | ARRAY STRING | The position at which to insert the value. |
value |
Yes | JSON | The JSON value to insert. |
insert_after |
Yes | Boolean | Controls insertion direction when input contains an array and the element at path exists. true inserts after the position; false inserts before it. Default: false. |
Return value
Returns the modified JSON document.
Usage notes
Array insertion rules: When inserting into an array, let n be the array length and m be the target index specified in path:
| Condition | Behavior |
|---|---|
m >= n |
Appended to the end of the array |
m < -n |
Prepended to the beginning of the array |
-n <= m < n |
Inserted at position m, relative to insert_after |
input contains no array |
Inserted at the end by default |
Examples
Insert into a JSON object
Add key t3 inside the nested object f2:
SELECT json_insert(
JSON'{"f1":1,"f2":{"t1":1,"t2":2}}',
array("f2","t3"),
JSON'3',
false
);Result: {"f1":1,"f2":{"t1":1,"t2":2,"t3":3}}
Insert into a JSON array
All examples below use {"f1":[0,1,2]} as input. The array has length 3, so n = 3.
Insert before or after a specific index (-n <= m < n):
-- Insert 10 before index 0 (insert_after = false)
SELECT json_insert(JSON'{"f1":[0,1,2]}', array("f1","0"), JSON'10', false);
-- Result: {"f1":[10,0,1,2]}
-- Insert 10 after index 0 (insert_after = true)
SELECT json_insert(JSON'{"f1":[0,1,2]}', array("f1","0"), JSON'10', true);
-- Result: {"f1":[0,10,1,2]}
-- Insert 10 before index 1 (insert_after = false)
SELECT json_insert(JSON'{"f1":[0,1,2]}', array("f1","1"), JSON'10', false);
-- Result: {"f1":[0,10,1,2]}Append to the end (m >= n, index 4 >= length 3):
SELECT json_insert(JSON'{"f1":[0,1,2]}', array("f1","4"), JSON'4', false);
-- Result: {"f1":[0,1,2,4]}Prepend to the beginning (m < -n, index -4 < -3):
SELECT json_insert(JSON'{"f1":[0,1,2]}', array("f1","-4"), JSON'4', false);
-- Result: {"f1":[4,0,1,2]}