Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.5Mb
PDF (A4) - 37.5Mb
PDF (RPM) - 36.9Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 211.3Kb
Man Pages (Zip) - 321.0Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Functions That Modify JSON Values

12.16.4 Functions That Modify JSON Values

The functions in this section modify JSON values and return the result.

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

    Appends values to the end of the indicated arrays within a JSON document and returns the result. This function was renamed to JSON_ARRAY_APPEND() in MySQL 5.7.9.

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

    Appends values to the end of the indicated arrays within a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

    The path/value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

    If a path selects a scalar or object value, that value is autowrapped within an array and the new value is added to that array. Pairs for which the path does not identify any value in the JSON document are ignored.

    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1]', 1);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1]', 1) |
    +----------------------------------+
    | ["a", ["b", "c", 1], "d"]        |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[0]', 2);
    +----------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[0]', 2) |
    +----------------------------------+
    | [["a", 2], ["b", "c"], "d"]      |
    +----------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$[1][0]', 3);
    +-------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$[1][0]', 3) |
    +-------------------------------------+
    | ["a", [["b", 3], "c"], "d"]         |
    +-------------------------------------+
    
    mysql> SET @j = '{"a": 1, "b": [2, 3], "c": 4}';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$.b', 'x');
    +------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$.b', 'x')  |
    +------------------------------------+
    | {"a": 1, "b": [2, 3, "x"], "c": 4} |
    +------------------------------------+
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$.c', 'y');
    +--------------------------------------+
    | JSON_ARRAY_APPEND(@j, '$.c', 'y')    |
    +--------------------------------------+
    | {"a": 1, "b": [2, 3], "c": [4, "y"]} |
    +--------------------------------------+
    
    mysql> SET @j = '{"a": 1}';
    mysql> SELECT JSON_ARRAY_APPEND(@j, '$', 'z');
    +---------------------------------+
    | JSON_ARRAY_APPEND(@j, '$', 'z') |
    +---------------------------------+
    | [{"a": 1}, "z"]                 |
    +---------------------------------+
    
  • JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

    Updates a JSON document, inserting into an array within the document and returning the modified document. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard or does not end with an array element identifier.

    The path/value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

    Pairs for which the path does not identify any array in the JSON document are ignored. If a path identifies an array element, the corresponding value is inserted at that element position, shifting any following values to the right. If a path identifies an array position past the end of an array, the value is inserted at the end of the array.

    mysql> SET @j = '["a", {"b": [1, 2]}, [3, 4]]';
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1]', 'x');
    +------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[1]', 'x') |
    +------------------------------------+
    | ["a", "x", {"b": [1, 2]}, [3, 4]]  |
    +------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[100]', 'x');
    +--------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[100]', 'x') |
    +--------------------------------------+
    | ["a", {"b": [1, 2]}, [3, 4], "x"]    |
    +--------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x');
    +-----------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[1].b[0]', 'x') |
    +-----------------------------------------+
    | ["a", {"b": ["x", 1, 2]}, [3, 4]]       |
    +-----------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[2][1]', 'y');
    +---------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[2][1]', 'y') |
    +---------------------------------------+
    | ["a", {"b": [1, 2]}, [3, "y", 4]]     |
    +---------------------------------------+
    mysql> SELECT JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y');
    +----------------------------------------------------+
    | JSON_ARRAY_INSERT(@j, '$[0]', 'x', '$[2][1]', 'y') |
    +----------------------------------------------------+
    | ["x", "a", {"b": [1, 2]}, [3, 4]]                  |
    +----------------------------------------------------+
    

    Earlier modifications affect the positions of the following elements in the array, so subsequent paths in the same JSON_ARRAY_INSERT() call should take this into account. In the final example, the second path inserts nothing because the path no longer matches anything after the first insert.

  • JSON_INSERT(json_doc, path, val[, path, val] ...)

    Inserts data into a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

    The path/value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

    A path/value pair for an existing path in the document is ignored and does not overwrite the existing document value. A path/value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

    • A member not present in an existing object. The member is added to the object and associated with the new value.

    • A position past the end of an existing array. The array is extended with the new value. If the existing value is not an array, it is autowrapped as an array, then extended with the new value.

    Otherwise, a path/value pair for a nonexisting path in the document is ignored and has no effect.

    For a comparison of JSON_INSERT(), JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
    +----------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
    +----------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
    +----------------------------------------------------+
    
  • JSON_MERGE(json_doc, json_doc[, json_doc] ...)

    Merges two or more JSON documents and returns the merged result. Returns NULL if any argument is NULL. An error occurs if any argument is not a valid JSON document.

    Merging takes place according to the following rules. For additional information, see Normalization, Merging, and Autowrapping of JSON Values.

    • Adjacent arrays are merged to a single array.

    • Adjacent objects are merged to a single object.

    • A scalar value is autowrapped as an array and merged as an array.

    • An adjacent array and object are merged by autowrapping the object as an array and merging the two arrays.

    mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
    +---------------------------------------+
    | JSON_MERGE('[1, 2]', '[true, false]') |
    +---------------------------------------+
    | [1, 2, true, false]                   |
    +---------------------------------------+
    mysql> SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}');
    +-------------------------------------------+
    | JSON_MERGE('{"name": "x"}', '{"id": 47}') |
    +-------------------------------------------+
    | {"id": 47, "name": "x"}                   |
    +-------------------------------------------+
    mysql> SELECT JSON_MERGE('1', 'true');
    +-------------------------+
    | JSON_MERGE('1', 'true') |
    +-------------------------+
    | [1, true]               |
    +-------------------------+
    mysql> SELECT JSON_MERGE('[1, 2]', '{"id": 47}');
    +------------------------------------+
    | JSON_MERGE('[1, 2]', '{"id": 47}') |
    +------------------------------------+
    | [1, 2, {"id": 47}]                 |
    +------------------------------------+
    
  • JSON_REMOVE(json_doc, path[, path] ...)

    Removes data from a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or is $ or contains a * or ** wildcard.

    The path arguments are evaluated left to right. The document produced by evaluating one path becomes the new value against which the next path is evaluated.

    It is not an error if the element to be removed does not exist in the document; in that case, the path does not affect the document.

    mysql> SET @j = '["a", ["b", "c"], "d"]';
    mysql> SELECT JSON_REMOVE(@j, '$[1]');
    +-------------------------+
    | JSON_REMOVE(@j, '$[1]') |
    +-------------------------+
    | ["a", "d"]              |
    +-------------------------+
    
  • JSON_REPLACE(json_doc, path, val[, path, val] ...)

    Replaces existing values in a JSON document and returns the result. Returns NULL if any argument is NULL. An error occurs if the json_doc argument is not a valid JSON document or any path argument is not a valid path expression or contains a * or ** wildcard.

    The path/value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

    A path/value pair for an existing path in the document overwrites the existing document value with the new value. A path/value pair for a nonexisting path in the document is ignored and has no effect.

    For a comparison of JSON_INSERT(), JSON_REPLACE(), and JSON_SET(), see the discussion of JSON_SET().

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------+
    | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------+
    | {"a": 10, "b": [2, 3]}                              |
    +-----------------------------------------------------+
    
  • JSON_SET(json_doc, path, val[, path, val] ...)

    Inserts or updates data in a JSON document and returns the result. Returns NULL if any argument is NULL or path, if given, does not locate an object. An error occurs if the json_doc argument is not a valid JSON document or the path argument is not a valid path expression or contains a * or ** wildcard.

    The path/value pairs are evaluated left to right. The document produced by evaluating one pair becomes the new value against which the next pair is evaluated.

    A path/value pair for an existing path in the document overwrites the existing document value with the new value. A path/value pair for a nonexisting path in the document adds the value to the document if the path identifies one of these types of values:

    • A member not present in an existing object. The member is added to the object and associated with the new value.

    • A position past the end of an existing array. The array is extended with the new value. If the existing value is not an array, it is autowrapped as an array, then extended with the new value.

    Otherwise, a path/value pair for a nonexisting path in the document is ignored and has no effect.

    The JSON_SET(), JSON_INSERT(), and JSON_REPLACE() functions are related:

    The following examples illustrate these differences, using one path that does exist in the document ($.a) and another that does not exist ($.c):

    mysql> SET @j = '{ "a": 1, "b": [2, 3]}';
    mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]');
    +-------------------------------------------------+
    | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') |
    +-------------------------------------------------+
    | {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
    +-------------------------------------------------+
    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]');
    +----------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') |
    +----------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
    +----------------------------------------------------+
    mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]');
    +-----------------------------------------------------+
    | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') |
    +-----------------------------------------------------+
    | {"a": 10, "b": [2, 3]}                              |
    +-----------------------------------------------------+
    
  • JSON_UNQUOTE(val)

    Unquotes JSON value and returns the result as a utf8mb4 string. Returns NULL if the argument is NULL. An error occurs if the value starts and ends with double quotes but is not a valid JSON string literal.

    Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (\), known as the escape character. MySQL recognizes the escape sequences shown in Table 12.21, “JSON_UNQUOTE() Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, \x is just x. These sequences are case sensitive. For example, \b is interpreted as a backspace, but \B is interpreted as B.

    Table 12.21 JSON_UNQUOTE() Special Character Escape Sequences

    Escape SequenceCharacter Represented by Sequence
    \" A double quote (") character
    \b A backspace character
    \f A formfeed character
    \n A newline (linefeed) character
    \r A carriage return character
    \t A tab character
    \\ A backslash (\) character
    \uXXXX UTF-8 bytes for Unicode value XXXX

    Two simple examples of the use of this function are shown here:

    mysql> SET @j = '"abc"';
    mysql> SELECT @j, JSON_UNQUOTE(@j);
    +-------+------------------+
    | @j    | JSON_UNQUOTE(@j) |
    +-------+------------------+
    | "abc" | abc              |
    +-------+------------------+
    mysql> SET @j = '[1, 2, 3]';
    mysql> SELECT @j, JSON_UNQUOTE(@j);
    +-----------+------------------+
    | @j        | JSON_UNQUOTE(@j) |
    +-----------+------------------+
    | [1, 2, 3] | [1, 2, 3]        |
    +-----------+------------------+
    

    The following set of examples shows how JSON_UNQUOTE handles escapes with NO_BACKSLASH_ESCAPES disabled and enabled:

    mysql> SELECT @@sql_mode;
    +------------+
    | @@sql_mode |
    +------------+
    |            |
    +------------+
    
    mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
    +------------------------------+
    | JSON_UNQUOTE('"\\t\\u0032"') |
    +------------------------------+
    |       2                           |
    +------------------------------+
    
    mysql> SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
    mysql> SELECT JSON_UNQUOTE('"\\t\\u0032"');
    +------------------------------+
    | JSON_UNQUOTE('"\\t\\u0032"') |
    +------------------------------+
    | \t\u0032                     |
    +------------------------------+
    
    mysql> SELECT JSON_UNQUOTE('"\t\u0032"');
    +----------------------------+
    | JSON_UNQUOTE('"\t\u0032"') |
    +----------------------------+
    |       2                         |
    +----------------------------+
    

User Comments
Sign Up Login You must be logged in to post a comment.