JSON_ARRAY_APPEND

Purpose

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

Syntax

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

Parameters

The json_doc parameter specifies the JSON document, and path is the path parameter. 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 will be automatically wrapped in an array, and the new value will be added to this array. Path-value pairs that do not identify an array in the JSON document will be ignored.

Examples

• In the following example, a user variable @jn is defined, containing a JSON string that represents a JSON array.

  obclient> 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.

  obclient> 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
  

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

  obclient> 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
  

  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.

  obclient> 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
  

• In the following example, a user variable @jemp is defined, containing a JSON string that represents an object. This 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.

  obclient> 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.

  obclient> 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
  

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

  obclient> 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
  

• In the following example, a user variable @temp is defined, containing a simple JSON object. In this object, the key is "a" and the value is the number 5.

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

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

  obclient> SELECT JSON_ARRAY_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_ARRAY_APPEND(@temp, '$', 'z') |
  +------------------------------------+
  | [{"a": 5}, "z"]                    |
  +------------------------------------+
  1 row in set
  

References

JSON_APPEND