JSON_SET_<type>
There is a group of three JSON functions, which sets the value of a given key in a JSON map or array. It requires the key path to exist and will only create the last key and value if it does not exist. It will not create the entire key path.
Maps are in the form:
{"item1":1, "item2":2, "item3": [3,4,5]}
And arrays are in the form:
[1, 2, 3]
JSON_SET_DOUBLE
is for all numeric data. Javascript has only one numeric type.JSON_SET_STRING
is for all text and binary types.JSON_SET_JSON
is for all valid Javascript types, includingtrue
,false
, JSON maps, and lists.
JSON_SET_<type>(json, keypath, value)
Arguments
json: a valid JSON map or array, or the name of a JSON column.
keypath: The path to the key to set value to (comma-separated list of dictionary keys or zero-indexed array positions).
value: the value to set
If the keyname does not exist in a map, it will be added. If it does exist, the value will be overwritten. If an array index is past the end of the array, intervening values will be set to NULL.
Return Value
The original json document, modified to set the specified key path to the specified value
SQL NULL if json is not a valid JSON map or array
json_compatibility_level
This function allow you to set values within a json document which return a new, logically modified document, while leaving the original document unchanged. The json_compatibility_level
engine variable controls the behavior of the json_set_<type>
functions and specifies whether the function will create the key if it does not exist. The default value for json_compatibility_level
is 7.8
, which means that missing keys are not created. To change the behavior so that missing keys are created, set json_compatibility_level
to 8.0
.
Important
The default value for the json_compatibility_level
behavior will change in an upcoming release. It is recommended to use level 8.0 for new development.
Examples
The following examples illustrate the output when json_compatibility_level
is set to 7.8
.
SELECT JSON_SET_DOUBLE('{"a":1, "b":2}', 'z', 26) AS set_z; +----------------------+ | set_z | +----------------------+ | {"a":1,"b":2,"z":26} | +----------------------+ SELECT JSON_SET_DOUBLE('[1,2,3]', 4, 26) AS set_4; +-----------------+ | set_4 | +-----------------+ | [1,2,3,null,26] | +-----------------+ SELECT JSON_SET_JSON('{"a":{"b":{"c": 1}}}', 'a', 'b', '[1,2,3]'); +------------------------------------------------------------+ | JSON_SET_JSON('{"a":{"b":{"c":1}}}', 'a', 'b', '[1,2,3]') | +------------------------------------------------------------+ | {"a":{"b":[1,2,3]}} | +------------------------------------------------------------+
SELECT JSON_SET_JSON('{"item1":null,"item2":[847,849]}', 'item1', 0, 'true') as item_numbers; +----------------------------------------+ | item_numbers | +----------------------------------------+ | {"item1":null,"item2":[847,849]}. | +----------------------------------------+
SELECT JSON_SET_JSON('[2,{"item2":false}]', 0, 'item2', null) as item_numbers; +----------------------------+ | item_numbers | +----------------------------+ | [2,{"item2":false}] | +----------------------------+
SELECT JSON_SET_JSON('{"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79}', 'item1', '0', 'x', 'true') as item_numbers; +------------------------------------------------------------------+ | item_numbers | +------------------------------------------------------------------+ | {"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79} | +------------------------------------------------------------------+
The following examples illustrate the output when json_compatability_level
is set to 8.0
.
SELECT JSON_SET_JSON('{"item1":null,"item2":[847,849]}', 'item1', 0, 'true') as item_numbers; +----------------------------------------+ | item_numbers | +----------------------------------------+ | {"item1":{"0":true},"item2":[847,849]} | +----------------------------------------+
SELECT JSON_SET_JSON('[2,{"item2":false}]', 0, 'item2', null) as item_numbers; +-----------------------+ | item_numbers | +-----------------------+ | [{},{“item2”:false}] | +-----------------------+
SELECT JSON_SET_JSON('{"item1":{"912":913,"item2":0},"item3":[112,212,318],"item4":79}', 'item1', '0', 'x', 'true') as item_numbers; +---------------------------------------------------------------------------------------------+ | item_numbers{“item1”:{“0":{“x”:true},“912":913,“item2”:0},“item3":[112,212,318],“item4”:79} | +---------------------------------------------------------------------------------------------+
Notice
A JSON or Javascript null value is distinct from SQL NULL.