JSON_APPEND

Description

This function appends a specified value to the end of a specified array in a JSON document and returns the result. It is an alias of the JSON_ARRAY_APPEND() function.

Syntax

JSON_APPEND(json_doc, path, val[, path, val] ...)

Remarks

The json_doc parameter specifies the JSON document, and path specifies the path. If any parameter is NULL, the function returns NULL.

The following situations will result in an error:

• The json_doc parameter is not a valid JSON document.

• The path parameter is not a valid path expression.

• The path parameter contains the * or ** wildcard.

The path-value pairs are evaluated from left to right. The document generated by evaluating the path-value pair is used as the new value for the next pair.

If the value corresponding to the path is a scalar or an object, it is automatically wrapped in an array, and the new value is added to the array. Path-value pairs that do not identify an array in the JSON document are ignored.

Examples

• The following example defines a user variable @jn that contains a JSON string representing a JSON array.

  SET @jn = '["a", ["b", "c"], "d","e"]';

  Append the value 1 to the end of the array at the path $[1] in the @jn variable.

  SELECT JSON_APPEND(@jn, '$[1]', 1);

  Here, $[1] corresponds to the second element in the JSON array. The result is as follows:

  +--------------------------------+
  | JSON_APPEND(@jn, '$[1]', 1)    |
  +--------------------------------+
  | ["a", ["b", "c", 1], "d", "e"] |
  +--------------------------------+
  1 row in set (0.001 sec)

  Append the value 2 to the end of the array at the path $[0] in the @jn variable.

  SELECT JSON_APPEND(@jn, '$[0]', 2);

  Here, $[0] corresponds to the first element in the JSON array. The result is as follows:

  +----------------------------------+
  | JSON_APPEND(@jn, '$[0]', 2)      |
  +----------------------------------+
  | [["a", 2], ["b", "c"], "d", "e"] |
  +----------------------------------+
  1 row in set (0.001 sec)

  Since ["b", "c"] is an array, we can append the value 3 to the end of the array at the path $[1][0] in the @jn variable.

  SELECT JSON_APPEND(@jn, '$[1][0]', 3);

  The result is as follows:

  +----------------------------------+
  | JSON_APPEND(@jn, '$[1][0]', 3)   |
  +----------------------------------+
  | ["a", [["b", 3], "c"], "d", "e"] |
  +----------------------------------+
  1 row in set (0.001 sec)

• The following example defines a user variable @jemp that contains a JSON string representing an object. The object has three key-value pairs: the value of "a" is the number 1; the value of "b" is an array containing the numbers 2 and 3; and the value of "c" is the number 4.

  SET @jemp = '{"a": 1, "b": [2, 3], "c": 4}';

  Append the string x to the end of the array at the path $.b in the @jemp variable.

  SELECT JSON_APPEND(@jemp, '$.b', 'x');

  Here, $.b corresponds to the array associated with the "b" key. The result is as follows:

  +------------------------------------+
  | JSON_APPEND(@jemp, '$.b', 'x')     |
  +------------------------------------+
  | {"a": 1, "b": [2, 3, "x"], "c": 4} |
  +------------------------------------+
  1 row in set (0.001 sec)

  Similarly, append the string y to the end of the array at the path $.c in the @jemp variable.

  SELECT JSON_APPEND(@jemp, '$.c', 'y');

  Here, $.c corresponds to the array associated with the "c" key. The result is as follows:

  +--------------------------------------+
  | JSON_APPEND(@jemp, '$.c', 'y')       |
  +--------------------------------------+
  | {"a": 1, "b": [2, 3], "c": [4, "y"]} |
  +--------------------------------------+
  1 row in set (0.001 sec)

• The following example defines a user variable @temp that contains a simple JSON object. In this object, the key is "a" and the value is the number 5.

  SET @temp = '{"a": 5}';

  Append the string z to the end of the array at the path $ in the @temp variable.

  SELECT JSON_APPEND(@temp, '$', 'z');

  Note that the path $ refers to the root level of the JSON document, which is the entire JSON object, not a key-value pair within the object. The result is as follows:

  +------------------------------+
  | JSON_APPEND(@temp, '$', 'z') |
  +------------------------------+
  | [{"a": 5}, "z"]              |
  +------------------------------+
  1 row in set (0.001 sec)

References

JSON_ARRAY_APPEND