JSON_ARRAY_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_APPEND() function.

Syntax

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

Instructions

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 a path-value pair is used as the new value for evaluating the next pair.

If the value corresponding to the path is a scalar or object value, it is automatically wrapped in an array, and the new value is added to the array. Path-value pairs in the JSON document that do not correspond to an array 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"]';

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

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

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

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

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

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

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

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

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

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

  The result is as follows:

  +--------------------------------------+
  | JSON_ARRAY_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 contains 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}';

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

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

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

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

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

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

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

  +--------------------------------------+
  | JSON_ARRAY_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}';

  The string z is appended to the end of the array at the path $ in the variable @temp.

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

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

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

References

JSON_APPEND