Purpose
This function appends a specified value to the end of an array in a JSON document and returns the result. It is an alias for the JSON_ARRAY_APPEND() function.
Syntax
JSON_APPEND(json_doc, path, val[, path, val] ...)
Description
The json_doc parameter specifies the JSON document, and path is a path parameter. If either parameter is NULL, NULL is returned.
An error will be returned in the following scenarios:
The
json_docparameter is not a valid JSON document.The
pathparameter is not a valid path expression.The
pathparameter contains the * or ** wildcard.
Path-value pairs are evaluated from left to right. The document generated by evaluating the current path-value pair becomes the new value for evaluating the next pair.
If the value corresponding to a path is a scalar or an object value, that value is automatically wrapped in an array and added to the array. Path-value pairs in the JSON document that are not identified by a path and belong to no array are ignored.
Examples
In the following example, a user variable
@jnis defined to contain a JSON string that represents a JSON array.obclient> SET @jn = '["a", ["b", "c"], "d","e"]';Append the value
1to the end of the array whose path is$[1]and whose variable is@jn.obclient> SELECT JSON_APPEND(@jn, '$[1]', 1);Here,
$[1]corresponds to the second element in the JSON array. The return result is as follows:+--------------------------------+ | JSON_APPEND(@jn, '$[1]', 1) | +--------------------------------+ | ["a", ["b", "c", 1], "d", "e"] | +--------------------------------+ 1 row in setAppend the value
2to the end of the array whose path is$[0]and whose variable is@jn.obclient> SELECT JSON_APPEND(@jn, '$[0]', 2);Here,
$[0]corresponds to the first element in the JSON array. The return result is as follows:+----------------------------------+ | JSON_APPEND(@jn, '$[0]', 2) | +----------------------------------+ | [["a", 2], ["b", "c"], "d", "e"] | +----------------------------------+ 1 row in setSince
["b", "c"]is an array, we can append the value3to the end of the array at path$[1][0]in variable@jn.obclient> SELECT JSON_APPEND(@jn, '$[1][0]', 3);The return result is as follows:
+----------------------------------+ | JSON_APPEND(@jn, '$[1][0]', 3) | +----------------------------------+ | ["a", [["b", 3], "c"], "d", "e"] | +----------------------------------+ 1 row in setIn the following example, a user variable
@jempis defined, containing a JSON string that represents an object. The object contains three key-value pairs: the value of"a"is the number1; the value of"b"is an array containing the numbers2and3; the value of"c"is the number4.obclient> SET @jemp = '{"a": 1, "b": [2, 3], "c": 4}';Appends the string
xto the end of the array whose path is$.band whose variable is@jemp.obclient> SELECT JSON_APPEND(@jemp, '$.b', 'x');Here,
$.brepresents the array corresponding to the"b"key. The return result is as follows:+------------------------------------+ | JSON_APPEND(@jemp, '$.b', 'x') | +------------------------------------+ | {"a": 1, "b": [2, 3, "x"], "c": 4} | +------------------------------------+ 1 row in setSimilarly, append the string
yto the end of the array with path$.cin the variable@jemp.obclient> SELECT JSON_APPEND(@jemp, '$.c', 'y');Here,
$.crepresents the array corresponding to the"c"key. The return result is as follows:+--------------------------------------+ | JSON_APPEND(@jemp, '$.c', 'y') | +--------------------------------------+ | {"a": 1, "b": [2, 3], "c": [4, "y"]} | +--------------------------------------+ 1 row in setIn the following example, a user variable
@tempis defined, containing a simple JSON object. In this object, the key is"a"and the value is the number5.obclient> SET @temp = '{"a": 5}';Appends the string
zto the end of the array with path$in the variable@temp.obclient> SELECT JSON_APPEND(@temp, '$', 'z');Note that specifying the path as
$refers to the root level of the JSON document, which is the entire JSON object, rather than adding key-value pairs to the JSON object. The return result is as follows:+------------------------------+ | JSON_APPEND(@temp, '$', 'z') | +------------------------------+ | [{"a": 5}, "z"] | +------------------------------+ 1 row in set