This is a set of three JSON functions, which sets the value of a given key in a JSON map or array.

Maps are in the form:

{"a":1, "b":2, "c": [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, including true, 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 complete map or array
• SQL NULL if json is not a valid JSON map or array

Examples

mysql> select json_set_double('{"a":1, "b":2}', 'z', 26) as set_z;
+----------------------+
| set_z                |
+----------------------+
| {"a":1,"b":2,"z":26} |
+----------------------+

mysql> select json_set_double('[1,2,3]', 4, 26) as set_4;
+-----------------+
| set_4           |
+-----------------+
| [1,2,3,null,26] |
+-----------------+

mysql> 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]}}                                        |
+------------------------------------------------------------+

Was this Article Helpful?

Thanks For Your Feedback!