JSON_APPEND

Purpose

JSON_APPEND() appends the specified values to the end of the specified arrays in a JSON document and returns the result. The function is an alias for JSON_ARRAY_APPEND().

Syntax

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

Description

json_doc specifies the name of the JSON document, and path specifies the path. If any argument is NULL, the return value is NULL.

An error occurs in the following cases:

• The JSON document specified for json_doc is invalid.

• The path expression specified for path is invalid.

• The specified path contains a * or ** wildcard.

Path-value pairs are evaluated from left to right. The document generated by using an evaluated path-value pair is used as a new value to evaluate the next pair.

If the value corresponding to the path is a scalar or object value, the value is automatically wrapped in an array, and the new value is added to the array. The path-value pairs for which the path does not identify any array in the JSON document are ignored.

Examples

• The following example defines a user variable named @jn, which contains a JSON string that represents a JSON array.

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

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

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

  Here, $[1] points 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 set
  

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

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

  Here, $[0] points 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 set
  

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

  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 set
  

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

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

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

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

  Here, $.b points to the array of 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 set
  

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

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

  Here, $.c points to the array of 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 set
  

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

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

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

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

  Note that the path $ indicates the root path of the JSON document. That is, the string is appended to the entire JSON object instead of being added as a key-value pair to the JSON object. The return result is as follows:

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

References

JSON_ARRAY_APPEND