Replaces or inserts a value at a specified position in a JSON document.
Syntax
JSON json_set(JSON <input>, ARRAY<STRING> <path>, JSON <value>, BOOLEAN <create_if_missing>);Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
input |
JSON | Yes | The JSON array or JSON object to modify. |
path |
ARRAY<STRING> | Yes | The target position, expressed as an array of strings where each element represents one level of nesting. To reference an array index, use its numeric string, such as "0" for the first element. |
value |
JSON | Yes | The JSON value to write at the target position. |
create_if_missing |
BOOLEAN | Yes | Controls behavior when the target position does not exist. Valid values: true (add a value at the specified position) and false (default; do not add a value if the position does not exist). |
Return value
Returns the modified JSON value.
Examples
Replace an existing value
Replace f1 (index 0, field f1) with [2,3,4]:
SELECT json_set(
JSON '[{"f1":1,"f2":null},2,null,3]',
array("0", "f1"),
JSON '[2,3,4]',
false
);Result:
[{"f1":[2,3,4],"f2":null},2,null,3]Attempt to replace a non-existent path (create_if_missing=false)
Path f3 does not exist in the first element. With create_if_missing set to false, the function returns the original JSON unchanged:
SELECT json_set(
JSON '[{"f1":1,"f2":null},2,null,3]',
array("0", "f3"),
JSON '[2,3,4]',
false
);Result:
[{"f1":1,"f2":null},2,null,3]Insert a value at a non-existent path (create_if_missing=true)
Path f3 does not exist in the first element. With create_if_missing set to true, the function adds f3 as a new key:
SELECT json_set(
JSON '[{"f1":1,"f2":null},2,null,3]',
array("0", "f3"),
JSON '[2,3,4]',
true
);Result:
[{"f1":1,"f2":null,"f3":[2,3,4]},2,null,3]