MySQL JSON function

Time:2021-2-25

catalog
  • 12.17.1 JSON function reference
  • 12.17.2 functions for creating JSON values
  • 12.17.3 functions for searching JSON values
  • 12.17.4 functions for modifying JSON values
  • 12.17.5 functions that return JSON value properties
  • 12.17.6 function of JSON utility

12.17.1 JSON function reference

Table 12.21 JSON functions

Name describe
-> Returns a value from the JSON column after evaluating the path; equivalent to JSON_ EXTRACT()。
->>(introduction 5.7.13) After evaluating the path and dereferencing the result, return the value from the JSON column; equivalent to JSON_ UNQUOTE(JSON_ EXTRACT())。
JSON_APPEND()(abandoned) Attaching data to a JSON document
JSON_ARRAY() Creating a JSON array
JSON_ARRAY_APPEND() Attaching data to a JSON document
JSON_ARRAY_INSERT() Insert JSON array
JSON_CONTAINS() Does the JSON document contain specific objects in the path
JSON_CONTAINS_PATH() Does the JSON document contain any data in the path
JSON_DEPTH() Maximum depth of JSON document
JSON_EXTRACT() Returning data from a JSON document
JSON_INSERT() Inserting data into a JSON document
JSON_KEYS() Key array in JSON document
JSON_LENGTH() The number of elements in a JSON document
JSON_MERGE()(5.7.22 is not recommended) Merge JSON documents and keep duplicate keys. JSON_ MERGE_ Obsolete synonym for preserve()
JSON_MERGE_PATCH()(introduction 5.7.22) Merge JSON documents and replace duplicate key values
JSON_MERGE_PRESERVE()(introduction 5.7.22) Merge JSON documents and keep duplicate keys
JSON_OBJECT() Creating a JSON object
JSON_PRETTY()(introduction 5.7.22) Print JSON documents in an easy to read format
JSON_QUOTE() Referencing JSON documents
JSON_REMOVE() Remove data from a JSON document
JSON_REPLACE() Replace values in JSON documents
JSON_SEARCH() Path of value in JSON document
JSON_SET() Inserting data into a JSON document
JSON_STORAGE_SIZE()(introduction 5.7.22) Space for storing binary representations of JSON documents
JSON_TYPE() JSON value type
JSON_UNQUOTE() Dereference JSON value
JSON_VALID() Is the JSON value valid

MySQL 5.7.22 and later support two aggregate JSON functionsJSON_ARRAYAGG()andJSON_OBJECTAGG(). For a description of these functions, see section 12.20 “set functions.”.

Also starting from MySQL 5.7.22:

  • You can use theJSON_PRETTY()Function to “beautifully print” JSON values in an easy to read format.
  • You can use theJSON_STORAGE_SIZE()

For a complete description of these two functions, see section 12.17.6, JSON utility functions.

12.17.2 functions for creating JSON values

The functions listed in this section consist of component elements that make up JSON values.

  • JSON_ARRAY([val[, val\] ...])

    Evaluates (possibly empty) a list of values and returns a JSON array containing those values.

    mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());
    +---------------------------------------------+
    | JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |
    +---------------------------------------------+
    | [1, "abc", null, true, "11:30:24.000000"]   |
    +---------------------------------------------+
  • JSON_OBJECT([key, val[, key, val\] ...])

    Evaluates the list of key value pairs, which may be empty, and returns the JSON object that contains them. If any key name isNULLOr the number of parameters is odd.

    mysql> SELECT JSON_OBJECT('id', 87, 'name', 'carrot');
    +-----------------------------------------+
    | JSON_OBJECT('id', 87, 'name', 'carrot') |
    +-----------------------------------------+
    | {"id": 87, "name": "carrot"}            |
    +-----------------------------------------+
  • JSON_QUOTE(string)

    This is done by wrapping a string in double quotation marks and escaping inner quotation marks and other charactersutf8mb4The string is quoted as a JSON value, and the result is returned as a string.NULLIf the parameter is, theNULL

    This function is usually used to generate valid JSON string text for inclusion in a JSON document.

    According to table 12.22 “JSON”_ Unquote() escape sequence of special characters, using backslashes to escape some special characters.

    mysql> SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');
    +--------------------+----------------------+
    | JSON_QUOTE('null') | JSON_QUOTE('"null"') |
    +--------------------+----------------------+
    | "null"             | "\"null\""           |
    +--------------------+----------------------+
    mysql> SELECT JSON_QUOTE('[1, 2, 3]');
    +-------------------------+
    | JSON_QUOTE('[1, 2, 3]') |
    +-------------------------+
    | "[1, 2, 3]"             |
    +-------------------------+

You can alsoJSONUse to cast values of other types to that type to get JSON values. For more information, see converting between JSON and non JSON values.CAST(value AS JSON)

Two aggregate functions (MySQL 5.7.22 and later) are provided to generate JSON values.JSON_ARRAYAGG()Returns the result set as a single JSON array, andJSON_OBJECTAGG()Returns the result set as a single JSON object. For more information, see section 12.20 aggregate functions.

12.17.3 functions for searching JSON values

The functions in this section perform a search operation on the JSON value to extract data from it, report whether the data is in a certain location or report the path of the data in it.

  • JSON_CONTAINS(target, candidate[, path\])

    Indicates whether the given candidate JSON document is included in the targetjson document by returning 1 or 0, or (if the path parameter is provided) whether a candidate object is found on a specific path within the target. returnNULLIs there any parameter that isNULL, or the path parameter does not identify a part of the target document. If the target or candidate is not a valid JSON document, or the path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    To check only if any data exists on the path, clickJSON_CONTAINS_PATH()Instead.

    The following rules define containment:

    • If and only if the candidate scalars are comparable and equal, they are included in the target scalar. If two scalar values have the sameJSON_TYPE()Type, they are comparable, but the value of the type is differentINTEGERandDECIMALThey can also be compared with each other.
    • The candidate array is included in the target array if and only if each element in the candidate object is included in an element of the target.
    • If and only if the candidate non array is contained in an element of the target, the candidate non array is contained in the target array.
    • A candidate object is included in the target object only if each keyword in the candidate object has a keyword with the same name in the target and the value associated with the candidate keyword is included in the value associated with the target keyword.

    Otherwise, the candidate values will not be included in the target document.

    mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
    mysql> SET @j2 = '1';
    mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
    +-------------------------------+
    | JSON_CONTAINS(@j, @j2, '$.a') |
    +-------------------------------+
    |                             1 |
    +-------------------------------+
    mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
    +-------------------------------+
    | JSON_CONTAINS(@j, @j2, '$.b') |
    +-------------------------------+
    |                             0 |
    +-------------------------------+
    
    mysql> SET @j2 = '{"d": 4}';
    mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
    +-------------------------------+
    | JSON_CONTAINS(@j, @j2, '$.a') |
    +-------------------------------+
    |                             0 |
    +-------------------------------+
    mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
    +-------------------------------+
    | JSON_CONTAINS(@j, @j2, '$.c') |
    +-------------------------------+
    |                             1 |
    +-------------------------------+
  • JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path\] ...)

    Returns 0 or 1 to indicate whether the JSON document contains data in the given path. returnNULLAre there any parametersNULL. If JSON_ Doc parameter is not a valid JSON document, any path parameter is not a valid path expression, or one_ or_ All is not'one'Or, errors will occur'all'

    To check for specific values on the path, seeJSON_CONTAINS()Instead.

    If there is no specified path in the document, the return value is 0. Otherwise, the return value depends on one_ or_ All parameter:

    • 'one': 1 if at least one path exists in the document, otherwise 0.
    • 'all': 1 if all paths exist in the document, otherwise 0.
    mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
    mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
    +---------------------------------------------+
    | JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
    +---------------------------------------------+
    |                                           1 |
    +---------------------------------------------+
    mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
    +---------------------------------------------+
    | JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
    +---------------------------------------------+
    |                                           0 |
    +---------------------------------------------+
    mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
    +----------------------------------------+
    | JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
    +----------------------------------------+
    |                                      1 |
    +----------------------------------------+
    mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
    +----------------------------------------+
    | JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
    +----------------------------------------+
    |                                      0 |
    +----------------------------------------+
  • JSON_EXTRACT(json_doc, path[, path\] ...)

    Returns data from the JSON document, which is selected from the part of the document that matches the path parameter. returnNULLAre there any parametersNULLFind the path to the value in the document. If JSON_ If the doc parameter is not a valid JSON document or any path parameter is not a valid path expression, an error occurs.

    The return value consists of all the values that the path parameter matches. If it is possible for these parameters to return multiple values, the matched values are automatically packaged as arrays in the order corresponding to the path where they were generated. Otherwise, the return value is a single matching value.

    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
    +--------------------------------------------+
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
    +--------------------------------------------+
    | 20                                         |
    +--------------------------------------------+
    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');
    +----------------------------------------------------+
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
    +----------------------------------------------------+
    | [20, 10]                                           |
    +----------------------------------------------------+
    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');
    +-----------------------------------------------+
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
    +-----------------------------------------------+
    | [30, 40]                                      |
    +-----------------------------------------------+

    MySQL 5.7.9 and later support->A shortcut to this function, used with 2 arguments, where on the left isJSONColumn identifier (not an expression), and on the right is the JSON path to match in the column.

  • column->path

    In MySQL 5.7.9 and later, when used with two parameters, the->Operator acts asJSON_EXTRACT()The two parameters are the column identifier on the left and the JSON path on the right (evaluated against the JSON document (column value)). You can use such expressions in place of column identifiers anywhere in an SQL statement.

    SELECTThe two statements shown here produce the same output:

    mysql> SELECT c, JSON_EXTRACT(c, "$.id"), g
         > FROM jemp
         > WHERE JSON_EXTRACT(c, "$.id") > 1
         > ORDER BY JSON_EXTRACT(c, "$.name");
    +-------------------------------+-----------+------+
    | c                             | c->"$.id" | g    |
    +-------------------------------+-----------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 |
    | {"id": "4", "name": "Betty"}  | "4"       |    4 |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 |
    +-------------------------------+-----------+------+
    3 rows in set (0.00 sec)
    
    mysql> SELECT c, c->"$.id", g
         > FROM jemp
         > WHERE c->"$.id" > 1
         > ORDER BY c->"$.name";
    +-------------------------------+-----------+------+
    | c                             | c->"$.id" | g    |
    +-------------------------------+-----------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 |
    | {"id": "4", "name": "Betty"}  | "4"       |    4 |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 |
    +-------------------------------+-----------+------+
    3 rows in set (0.00 sec)

    This function is not limited toSELECT, as follows:

    mysql> ALTER TABLE jemp ADD COLUMN n INT;
    Query OK, 0 rows affected (0.68 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";
    Query OK, 1 row affected (0.04 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT c, c->"$.id", g, n
         > FROM jemp
         > WHERE JSON_EXTRACT(c, "$.id") > 1
         > ORDER BY c->"$.name";
    +-------------------------------+-----------+------+------+
    | c                             | c->"$.id" | g    | n    |
    +-------------------------------+-----------+------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
    | {"id": "4", "name": "Betty"}  | "4"       |    4 |    1 |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
    +-------------------------------+-----------+------+------+
    3 rows in set (0.00 sec)
    
    mysql> DELETE FROM jemp WHERE c->"$.id" = "4";
    Query OK, 1 row affected (0.04 sec)
    
    mysql> SELECT c, c->"$.id", g, n
         > FROM jemp
         > WHERE JSON_EXTRACT(c, "$.id") > 1
         > ORDER BY c->"$.name";
    +-------------------------------+-----------+------+------+
    | c                             | c->"$.id" | g    | n    |
    +-------------------------------+-----------+------+------+
    | {"id": "3", "name": "Barney"} | "3"       |    3 | NULL |
    | {"id": "2", "name": "Wilma"}  | "2"       |    2 | NULL |
    +-------------------------------+-----------+------+------+
    2 rows in set (0.00 sec)

    (for the statements used to create and fill the table just displayed, see index generated columns to provide a JSON column index.)

    This also applies to JSON array values, as follows:

    mysql> CREATE TABLE tj10 (a JSON, b INT);
    Query OK, 0 rows affected (0.26 sec)
    
    mysql> INSERT INTO tj10
         > VALUES ("[3,10,5,17,44]", 33), ("[3,10,5,17,[22,44,66]]", 0);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> SELECT a->"$[4]" FROM tj10;
    +--------------+
    | a->"$[4]"    |
    +--------------+
    | 44           |
    | [22, 44, 66] |
    +--------------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT * FROM tj10 WHERE a->"$[0]" = 3;
    +------------------------------+------+
    | a                            | b    |
    +------------------------------+------+
    | [3, 10, 5, 17, 44]           |   33 |
    | [3, 10, 5, 17, [22, 44, 66]] |    0 |
    +------------------------------+------+
    2 rows in set (0.00 sec)

    Nested arrays are supported. Of the expression used->Evaluation is likeNULLThe matching key cannot be found in the target JSON document, as follows:

    mysql> SELECT * FROM tj10 WHERE a->"$[4][1]" IS NOT NULL;
    +------------------------------+------+
    | a                            | b    |
    +------------------------------+------+
    | [3, 10, 5, 17, [22, 44, 66]] |    0 |
    +------------------------------+------+
    
    mysql> SELECT a->"$[4][1]" FROM tj10;
    +--------------+
    | a->"$[4][1]" |
    +--------------+
    | NULL         |
    | 44           |
    +--------------+
    2 rows in set (0.00 sec)

    This is the same as in useJSON_EXTRACT()You see the same behavior when:

    mysql> SELECT JSON_EXTRACT(a, "$[4][1]") FROM tj10;
    +----------------------------+
    | JSON_EXTRACT(a, "$[4][1]") |
    +----------------------------+
    | NULL                       |
    | 44                         |
    +----------------------------+
    2 rows in set (0.00 sec)
  • column->>path

    This is an improved, unreferenced extraction operator available in MySQL 5.7.13 and later. and->When the operator simply extracts the value of,->>In addition, unquotes is used to extract the result. In other words, given aJSONColumn value and a path expression path. The following three expressions return the same value:

    • JSON_UNQUOTE( JSON_EXTRACT(column, path) )
    • JSON_UNQUOTE(column -> path)
    • column->>path

    ->>sureJSON_UNQUOTE(JSON_EXTRACT())Use the operator wherever allowed. This includes (but is not limited to)SELECTList,WHEREandHAVINGClause, andORDER BYandGROUP BYClause.

    The next few sentences demonstrate this->>AndmysqlSome operator equivalents of other expressions in the client:

    mysql> SELECT * FROM jemp WHERE g > 2;
    +-------------------------------+------+
    | c                             | g    |
    +-------------------------------+------+
    | {"id": "3", "name": "Barney"} |    3 |
    | {"id": "4", "name": "Betty"}  |    4 |
    +-------------------------------+------+
    2 rows in set (0.01 sec)
    
    mysql> SELECT c->'$.name' AS name
        ->     FROM jemp WHERE g > 2;
    +----------+
    | name     |
    +----------+
    | "Barney" |
    | "Betty"  |
    +----------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
        ->     FROM jemp WHERE g > 2;
    +--------+
    | name   |
    +--------+
    | Barney |
    | Betty  |
    +--------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT c->>'$.name' AS name
        ->     FROM jemp WHERE g > 2;
    +--------+
    | name   |
    +--------+
    | Barney |
    | Betty  |
    +--------+
    2 rows in set (0.00 sec)

    See indexing a generated column to provide a JSON column index forjempCreate and fill in the SQL statements for the table in the sample set just shown.

    This operator can also be used with JSON arrays, as follows:

    mysql> CREATE TABLE tj10 (a JSON, b INT);
    Query OK, 0 rows affected (0.26 sec)
    
    mysql> INSERT INTO tj10 VALUES
        ->     ('[3,10,5,"x",44]', 33),
        ->     ('[3,10,5,17,[22,"y",66]]', 0);
    Query OK, 2 rows affected (0.04 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> SELECT a->"$[3]", a->"$[4][1]" FROM tj10;
    +-----------+--------------+
    | a->"$[3]" | a->"$[4][1]" |
    +-----------+--------------+
    | "x"       | NULL         |
    | 17        | "y"          |
    +-----------+--------------+
    2 rows in set (0.00 sec)
    
    mysql> SELECT a->>"$[3]", a->>"$[4][1]" FROM tj10;
    +------------+---------------+
    | a->>"$[3]" | a->>"$[4][1]" |
    +------------+---------------+
    | x          | NULL          |
    | 17         | y             |
    +------------+---------------+
    2 rows in set (0.00 sec)

    Same as->->>Operators are always extended in the output ofEXPLAIN, as shown in the following example:

    mysql> EXPLAIN SELECT c->>'$.name' AS name
        ->     FROM jemp WHERE g > 2\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: jemp
       partitions: NULL
             type: range
    possible_keys: i
              key: i
          key_len: 5
              ref: NULL
             rows: 2
         filtered: 100.00
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Note
       Code: 1003
    Message: /* select#1 */ select
    json_unquote(json_extract(`jtest`.`jemp`.`c`,'$.name')) AS `name` from
    `jtest`.`jemp` where (`jtest`.`jemp`.`g` > 2)
    1 row in set (0.00 sec)

    This is similar to MySQL->The way operators are extended in the same case.

    The->>The operator was added in MySQL 5.7.13.

  • JSON_KEYS(json_doc[, path\])

    Returns the key in the top-level value of the JSON object in the form of a JSON array, or, if the path parameter is provided, the top-level key in the selected path.NULLIf any parameter isNULL, the JSON_ The doc parameter is not an object, or if path is given, the object is not located. If JSON_ If the doc parameter is not a valid JSON document, or the path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    If the selected object is empty, the result array is empty. If the top-level value has nested subobjects, the return value does not contain the key from those subobjects.

    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}');
    +---------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
    +---------------------------------------+
    | ["a", "b"]                            |
    +---------------------------------------+
    mysql> SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b');
    +----------------------------------------------+
    | JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b') |
    +----------------------------------------------+
    | ["c"]                                        |
    +----------------------------------------------+
  • JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path\] ...])

    Returns the path of the given string in the JSON document.NULLIf either, or the parameter is JSON_ Doc, the file does not exist or cannot be found. If the parameter is not a valid JSON document, any parameter is not a valid path expression, or is not a constant expression, an error will occur. search_ strpathNULLpathsearch_strjson_docpathone_or_all'one'``'all'escape_char

    This one_ or_ The all parameter affects the search as follows:

    • 'one': the search terminates after the first match and returns a path string. Which match to consider first is not defined.
    • 'all': the search returns all matching path strings so that duplicate paths are not included. If there are multiple strings, they are automatically wrapped as an array. The order of array elements is uncertain.

    In search_ STR search string parameter,%and_Characters andLIKEOperators work the same way:%Matches any number of characters (including zero characters) and_Match exactly one character.

    To specify text in a search string%or_Character, please add escape character before it. The default value is\, if escape_ Char parameter missing or invalidNULL. Otherwise, escape_ Char must be empty or a constant of one character.

    For more information about matching and escaping character behavior, see the instructions forLIKEIn section 12.7.1, “string comparison functions and operators.”. For escape character processing, andLIKEThe difference in behavior is that escape charactersJSON_SEARCH()Must be evaluated as a constant at compile time, not just at execution time. For example, ifJSON_SEARCH()Escape in the prepared statement_ Char uses and?If the parameter is provided with a parameter, the parameter value may be constant at execution time, but not at compile time.

    mysql> SET @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
    
    mysql> SELECT JSON_SEARCH(@j, 'one', 'abc');
    +-------------------------------+
    | JSON_SEARCH(@j, 'one', 'abc') |
    +-------------------------------+
    | "$[0]"                        |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', 'abc');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', 'abc') |
    +-------------------------------+
    | ["$[0]", "$[2].x"]            |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', 'ghi');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', 'ghi') |
    +-------------------------------+
    | NULL                          |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10');
    +------------------------------+
    | JSON_SEARCH(@j, 'all', '10') |
    +------------------------------+
    | "$[1][0].k"                  |
    +------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$');
    +-----------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$') |
    +-----------------------------------------+
    | "$[1][0].k"                             |
    +-----------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*]');
    +--------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[*]') |
    +--------------------------------------------+
    | "$[1][0].k"                                |
    +--------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$**.k');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$**.k') |
    +---------------------------------------------+
    | "$[1][0].k"                                 |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k');
    +-------------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[*][0].k') |
    +-------------------------------------------------+
    | "$[1][0].k"                                     |
    +-------------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1]');
    +--------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[1]') |
    +--------------------------------------------+
    | "$[1][0].k"                                |
    +--------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]');
    +-----------------------------------------------+
    | JSON_SEARCH(@j, 'all', '10', NULL, '$[1][0]') |
    +-----------------------------------------------+
    | "$[1][0].k"                                   |
    +-----------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', 'abc', NULL, '$[2]') |
    +---------------------------------------------+
    | "$[2].x"                                    |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%a%');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', '%a%') |
    +-------------------------------+
    | ["$[0]", "$[2].x"]            |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%');
    +-------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%') |
    +-------------------------------+
    | ["$[0]", "$[2].x", "$[3].y"]  |
    +-------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[0]') |
    +---------------------------------------------+
    | "$[0]"                                      |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[2]') |
    +---------------------------------------------+
    | "$[2].x"                                    |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]');
    +---------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', NULL, '$[1]') |
    +---------------------------------------------+
    | NULL                                        |
    +---------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[1]');
    +-------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
    +-------------------------------------------+
    | NULL                                      |
    +-------------------------------------------+
    
    mysql> SELECT JSON_SEARCH(@j, 'all', '%b%', '', '$[3]');
    +-------------------------------------------+
    | JSON_SEARCH(@j, 'all', '%b%', '', '$[3]') |
    +-------------------------------------------+
    | "$[3].y"                                  |
    +-------------------------------------------+

    For more information about the path syntax of MySQL supporting JSON, including details about wildcard operation rules, please refer to the JSON path syntax.

12.17.4 functions for modifying JSON values

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

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

    Appends the value to the end of the specified array in the JSON document and returns the result. This functionJSON_ARRAY_APPEND()Renamed in MySQL 5.7.9; aliasJSON_APPEND()Now it has been discarded in MySQL 5.7 and deleted in MySQL 8.0.

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

    Appends the value to the end of the specified array in the JSON document and returns the result. returnNULLAre there any parametersNULL. If JSON_ If the doc parameter is not a valid JSON document or any path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    Path value pairs are evaluated from left to right. By evaluating a pair of generated documents will become a new value, in order to evaluate the next pair.

    If the path selects a scalar or object value, the value is automatically wrapped in the array and a new value is added to the array. The path cannot identify any pairs of values in the JSON document and will be 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\] ...)

    Update the JSON document, insert it into the array in the document, and return the modified document. returnNULLAre there any parametersNULL. If JSON_ If the doc parameter is not a valid JSON document, or any path parameter is not a valid path expression, or contains’ or wildcards, or does not end with an array element identifier, an error will occur.

    Path value pairs are evaluated from left to right. By evaluating a pair of generated documents will become a new value, in order to evaluate the next pair.

    The path cannot identify any pairs of arrays in the JSON document and will be ignored. If the path identifies an array element, the corresponding value is inserted into the element position, and then all subsequent values are moved to the right. If the path identifies an array position beyond the end of the 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 changes affect the position of subsequent elements in the array, so they are in the same arrayJSON_ARRAY_INSERT()Subsequent paths in the call should take this into account. In the last example, the second path inserts nothing because it no longer matches anything after the first insertion.

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

    Insert the data into the JSON document and return the result. returnNULLAre there any parametersNULL. If JSON_ If the doc parameter is not a valid JSON document or any path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    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]"}        |
    +----------------------------------------------------+

    The third and last value listed in the result is a quoted string, not an array like the second (without quotes in the output); the value is not cast to a JSON type. To insert an array as an array, you must explicitly perform such a conversion, as follows:

    mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON));
    +------------------------------------------------------------------+
    | JSON_INSERT(@j, '$.a', 10, '$.c', CAST('[true, false]' AS JSON)) |
    +------------------------------------------------------------------+
    | {"a": 1, "b": [2, 3], "c": [true, false]}                        |
    +------------------------------------------------------------------+
    1 row in set (0.00 sec)
  • JSON_MERGE(json_doc, json_doc[, json_doc\] ...)

    Merge two or more JSON documents. synonymJSON_MERGE_PRESERVE(); has been discarded in MySQL 5.7.22 and may be removed in a future version.

    mysql> SELECT JSON_MERGE('[1, 2]', '[true, false]');
    +---------------------------------------+
    | JSON_MERGE('[1, 2]', '[true, false]') |
    +---------------------------------------+
    | [1, 2, true, false]                   |
    +---------------------------------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Warning
       Code: 1287
    Message: 'JSON_MERGE' is deprecated and will be removed in a future release. \
     Please use JSON_MERGE_PRESERVE/JSON_MERGE_PATCH instead
    1 row in set (0.00 sec)

    For other examples, see the entry forJSON_MERGE_PRESERVE()

  • JSON_MERGE_PATCH(json_doc, json_doc[, json_doc\] ...)

    Perform a RFC 7396 compliant merge on two or more JSON documents and return the result of the merge without retaining members with duplicate keys. If at least one document passed as a parameter to the function is invalid, an error is thrown.

    be careful

    A description and example of the difference between this function andJSON_MERGE_PRESERVE(), see JSON_ MERGE_ Patch() and JSON_ MERGE_ Comparison of preserve().

    JSON_MERGE_PATCH()Perform the following consolidation:

    1. If the first parameter is not an object, the result of merging is the same as merging an empty object with the second parameter.
    2. If the second parameter is not an object, the merged result is the second parameter.
    3. If both parameters are objects, the merge result is an object with the following members:
      • All members of the first object do not have corresponding members with the same key in the second object.
      • All members of the second object have no corresponding keys in the first object, and their values are not JSONnullwritten words.
      • Has a value that exists in both the first and second objects, and the value in the second object is not JSONnullText for all members of the key. The values of these members are the result of a recursive combination of the values in the first object and the values in the second object.

    For more information, see normalization, merging, and automatic wrapping of JSON values.

    mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '[true, false]');
    +---------------------------------------------+
    | JSON_MERGE_PATCH('[1, 2]', '[true, false]') |
    +---------------------------------------------+
    | [true, false]                               |
    +---------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}');
    +-------------------------------------------------+
    | JSON_MERGE_PATCH('{"name": "x"}', '{"id": 47}') |
    +-------------------------------------------------+
    | {"id": 47, "name": "x"}                         |
    +-------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('1', 'true');
    +-------------------------------+
    | JSON_MERGE_PATCH('1', 'true') |
    +-------------------------------+
    | true                          |
    +-------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('[1, 2]', '{"id": 47}');
    +------------------------------------------+
    | JSON_MERGE_PATCH('[1, 2]', '{"id": 47}') |
    +------------------------------------------+
    | {"id": 47}                               |
    +------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }',
         >     '{ "a": 3, "c":4 }');
    +-----------------------------------------------------------+
    | JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
    +-----------------------------------------------------------+
    | {"a": 3, "b": 2, "c": 4}                                  |
    +-----------------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
         >     '{ "a": 5, "d":6 }');
    +-------------------------------------------------------------------------------+
    | JSON_MERGE_PATCH('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
    +-------------------------------------------------------------------------------+
    | {"a": 5, "b": 2, "c": 4, "d": 6}                                              |
    +-------------------------------------------------------------------------------+

    You can use this function tonullIn the seed parameter, specify the value of the same member to delete the member, as follows:

    mysql> SELECT JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}');
    +--------------------------------------------------+
    | JSON_MERGE_PATCH('{"a":1, "b":2}', '{"b":null}') |
    +--------------------------------------------------+
    | {"a": 1}                                         |
    +--------------------------------------------------+

    This example shows that the function runs recursively; that is, the value of a member is not limited to scalars, but can itself be a JSON document:

    mysql> SELECT JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}');
    +----------------------------------------------------+
    | JSON_MERGE_PATCH('{"a":{"x":1}}', '{"a":{"y":2}}') |
    +----------------------------------------------------+
    | {"a": {"x": 1, "y": 2}}                            |
    +----------------------------------------------------+

    JSON_MERGE_PATCH()It is supported in MySQL 5.7.22 and later.

    JSON_ MERGE_ Patch() and JSON_ MERGE_ Comparison of preserve().The behavior of and the behavior ofJSON_MERGE_PATCH()identicalJSON_MERGE_PRESERVE()With two exceptions:

    • JSON_MERGE_PATCH()Delete any member in the first object that matches the key in the second object, provided the value associated with the key in the second object is not JSONnull
    • If the members of the second object have keys that match the members of the first object, thenJSON_MERGE_PATCH() useThe value in the second objectreplaceThe value in the first object, andJSON_MERGE_PRESERVE() takeSecond valueadditionalTo the first value.

    This example compares two identical JSON objects (each with a matching key)"a")Merge results with the following two functions:

    mysql> SET @x = '{ "a": 1, "b": 2 }',
         >     @y = '{ "a": 3, "c": 4 }',
         >     @z = '{ "a": 5, "d": 6 }';
    
    mysql> SELECT  JSON_MERGE_PATCH(@x, @y, @z)    AS Patch,
        ->         JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
    *************************** 1. row ***************************
       Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
    Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
  • JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc\] ...)

    Merge two or more JSON documents and return the result of the merge. returnNULLAre there any parametersNULL. If any parameter is not a valid JSON document, an error occurs.

    The merger is carried out according to the following rules. For more information, see normalization, merging, and automatic wrapping of JSON values.

    • Adjacent arrays are merged into one array.
    • Adjacent objects are merged into a single object.
    • Scalar values are automatically packaged as arrays and then merged into arrays.
    • By automatically wrapping an object as an array and merging two arrays, you can merge adjacent arrays and objects.
    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');
    +------------------------------------------------+
    | JSON_MERGE_PRESERVE('[1, 2]', '[true, false]') |
    +------------------------------------------------+
    | [1, 2, true, false]                            |
    +------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}');
    +----------------------------------------------------+
    | JSON_MERGE_PRESERVE('{"name": "x"}', '{"id": 47}') |
    +----------------------------------------------------+
    | {"id": 47, "name": "x"}                            |
    +----------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('1', 'true');
    +----------------------------------+
    | JSON_MERGE_PRESERVE('1', 'true') |
    +----------------------------------+
    | [1, true]                        |
    +----------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}');
    +---------------------------------------------+
    | JSON_MERGE_PRESERVE('[1, 2]', '{"id": 47}') |
    +---------------------------------------------+
    | [1, 2, {"id": 47}]                          |
    +---------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }',
         >    '{ "a": 3, "c": 4 }');
    +--------------------------------------------------------------+
    | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c":4 }') |
    +--------------------------------------------------------------+
    | {"a": [1, 3], "b": 2, "c": 4}                                |
    +--------------------------------------------------------------+
    
    mysql> SELECT JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }',
         >    '{ "a": 5, "d": 6 }');
    +----------------------------------------------------------------------------------+
    | JSON_MERGE_PRESERVE('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }') |
    +----------------------------------------------------------------------------------+
    | {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}                                         |
    +----------------------------------------------------------------------------------+

    This function has been added as a synonym of in MySQL 5.7.22JSON_MERGE()JSON_MERGE()This feature is not recommended now, and may be removed in future versions of MySQL.

    This function is similar to but differs from JSON_MERGE_PATCH() in significant respects; see JSON_MERGE_PATCH() compared with JSON_MERGE_PRESERVE(), for more information.

  • 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 parameter is evaluated from left to right. The document generated by evaluating one path becomes the new value on which the next path is evaluated.

    If the element to be deleted does not exist in the document, this is not an error; in this 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\] ...)

    Replace the existing value in the JSON document and return the result. returnNULLAre there any parametersNULL. If JSON_ If the doc parameter is not a valid JSON document or any path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    Path value pairs are evaluated from left to right. By evaluating a pair of generated documents will become a new value, in order to evaluate the next pair.

    The path value pair of an existing path in the document uses the new value to override the existing document value. Path value pairs for paths that do not exist in the document are ignored and are not valid.

    For comparisonJSON_INSERT()JSON_REPLACE()as well asJSON_SET(), see the discussionJSON_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\] ...)

    Insert or update the data in the JSON document and return the result.NULLIf any parameter isNULLOr path, it returns. If given, it does not locate the object. If JSON_ If the doc parameter is not a valid JSON document or any path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    Path value pairs are evaluated from left to right. By evaluating a pair of generated documents will become a new value, in order to evaluate the next pair.

    The path value pair of an existing path in the document uses the new value to override the existing document value. A path value pair for a path that does not exist in the document adds a value to the document if it identifies one of the following types of values in the path:

    • A member that does not exist in an existing object. Members are added to the object and associated with the new value.
    • The position at which the super appears at the end of the array. Expand the array with new values. If the existing value is not an array, it is automatically wrapped as an array and then extended with the new value.

    Otherwise, the path value pairs of paths that do not exist in the document are ignored and invalid.

    OfJSON_SET()JSON_INSERT()andJSON_REPLACE()The relationship between function and function

    • JSON_SET()Replace existing values and add values that do not exist.
    • JSON_INSERT()Inserts a value without replacing an existing value.
    • JSON_REPLACE()onlyReplace the existing value.

    The following example uses a path that exists in the document($.a)And another path that doesn’t exist () illustrates these differences$.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(json_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.

    In a string, some sequences have special meanings unlessNO_BACKSLASH_ESCAPESSQL mode is enabled. Each sequence is marked with a backslash(\)At the beginning, i.eEscape character. MySQL can recognize table 12.22 “JSON”_ Unquote() special character escape sequence. Backslashes are ignored for all other escape sequences. That is, the escape character is interpreted as if there is no escape. For example,\xis x. These sequences are case sensitive. For example,\bIs interpreted as backspace, but\BIs interpreted asB

    Table 12.22 JSON_ Unquote() special character escape sequence

    Escape sequence The character represented by the sequence
    \" Double quotation marks(")Character
    \b Backspace character
    \f Page feed
    \n Line feed (line feed)
    \r Carriage return
    \t Tab
    \\ Backslash(\)Character
    \uXXXX UTF-8 byte of Unicode value

    Here are two simple examples of using this function:

    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 sample set shows how toJSON_UNQUOTEstayNO_BACKSLASH_ESCAPESEscape when disabled or 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                         |
    +----------------------------+

12.17.5 functions that return JSON value properties

The functions in this section return properties of the JSON value.

  • JSON_DEPTH(json_doc)

    Returns the maximum depth of the JSON document.NULLIf the parameter is, theNULL. If the parameter is not a valid JSON document, an error occurs.

    The depth of an empty array, empty object, or scalar value is 1. The depth of a non empty array containing only elements of depth 1 or a non empty object containing only member values of depth 1 is 2. Otherwise, the depth of the JSON document is greater than 2.

    mysql> SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true');
    +------------------+------------------+--------------------+
    | JSON_DEPTH('{}') | JSON_DEPTH('[]') | JSON_DEPTH('true') |
    +------------------+------------------+--------------------+
    |                1 |                1 |                  1 |
    +------------------+------------------+--------------------+
    mysql> SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]');
    +------------------------+------------------------+
    | JSON_DEPTH('[10, 20]') | JSON_DEPTH('[[], {}]') |
    +------------------------+------------------------+
    |                      2 |                      2 |
    +------------------------+------------------------+
    mysql> SELECT JSON_DEPTH('[10, {"a": 20}]');
    +-------------------------------+
    | JSON_DEPTH('[10, {"a": 20}]') |
    +-------------------------------+
    |                             3 |
    +-------------------------------+
  • JSON_LENGTH(json_doc[, path\])

    Returns the length of the JSON document, or, if the path parameter is provided, the length of the value in the document identified by the path. returnNULLAre there any independent variablesNULLOr the path variable cannot identify a value in the document. If JSON_ If the doc parameter is not a valid JSON document, or the path parameter is not a valid path expression or contains’ or wildcards, an error will occur.

    The file length is determined as follows:

    • The length of the scalar is 1.
    • The length of an array is the number of array elements.
    • The length of an object is the number of object members.
    • The length does not count the length of a nested array or object.
    mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');
    +---------------------------------+
    | JSON_LENGTH('[1, 2, {"a": 3}]') |
    +---------------------------------+
    |                               3 |
    +---------------------------------+
    mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');
    +-----------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |
    +-----------------------------------------+
    |                                       2 |
    +-----------------------------------------+
    mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');
    +------------------------------------------------+
    | JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |
    +------------------------------------------------+
    |                                              1 |
    +------------------------------------------------+
  • JSON_TYPE(json_val)

    returnutf8mb4A string indicating the JSON value type. It can be of object, array or scalar type, as follows:

    mysql> SET @j = '{"a": [10, true]}';
    mysql> SELECT JSON_TYPE(@j);
    +---------------+
    | JSON_TYPE(@j) |
    +---------------+
    | OBJECT        |
    +---------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));
    +------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |
    +------------------------------------+
    | ARRAY                              |
    +------------------------------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));
    +---------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |
    +---------------------------------------+
    | INTEGER                               |
    +---------------------------------------+
    mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));
    +---------------------------------------+
    | JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |
    +---------------------------------------+
    | BOOLEAN                               |
    +---------------------------------------+

    JSON_TYPE()returnNULLIs the parameterNULL

    mysql> SELECT JSON_TYPE(NULL);
    +-----------------+
    | JSON_TYPE(NULL) |
    +-----------------+
    | NULL            |
    +-----------------+

    If the parameter is not a valid JSON value, an error occurs:

    mysql> SELECT JSON_TYPE(1);
    ERROR 3146 (22032): Invalid data type for JSON data in argument 1
    to function json_type; a JSON string or JSON type is required.

    aboutNULLNon error results, the following list describes the possibleJSON_TYPE()Return value:

    • Pure JSON type:
      • OBJECT: JSON object
      • ARRAY: JSON array
      • BOOLEAN: JSON correct and wrong text
      • NULL: JSON empty text
    • Value type:
      • INTEGER: MySQL’sTINYINTSMALLINTMEDIUMINTandINTandBIGINTscalar
      • DOUBLE: MySQL scalarDOUBLE FLOAT
      • DECIMAL:MySQL DECIMALandNUMERICscalar
    • Time type:
      • DATETIME:MySQL DATETIMEandTIMESTAMPscalar
      • DATE:MySQL DATEscalar
      • TIME:MySQL TIMEscalar
    • String type:
      • STRING: MySQL’sutf8Character type scalar:CHARVARCHARTEXTENUM, andSET
    • Binary type:
      • BLOB: MySQL binary type scalar:BINARYVARBINARYBLOB
      • BIT:MySQL BITscalar
    • All other types:
      • OPAQUE(original bit)
  • JSON_VALID(val)

    Returns 0 or 1 to indicate whether the value is valid JSON.NULLIf the parameter is, theNULL

    mysql> SELECT JSON_VALID('{"a": 1}');
    +------------------------+
    | JSON_VALID('{"a": 1}') |
    +------------------------+
    |                      1 |
    +------------------------+
    mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');
    +---------------------+-----------------------+
    | JSON_VALID('hello') | JSON_VALID('"hello"') |
    +---------------------+-----------------------+
    |                   0 |                     1 |
    +---------------------+-----------------------+

12.17.6 function of JSON utility

This section describes utility functions that act on JSON values, or strings that can be resolved to JSON values.JSON_PRETTY()Output JSON values in an easy to read format.JSON_STORAGE_SIZE()Displays the amount of storage space used for a given JSON value.

  • JSON_PRETTY(json_val)

    Provide beautiful JSON value printing, similar to the JSON value implemented by PHP and other languages and database systems. The value provided must be a JSON value or a valid string representation of a JSON value. Extra spaces and newlines in this value have no effect on the output. aboutNULLValue, the function returnsNULL. If the value is not a JSON document or cannot be resolved to a document, the function fails with an error.

    The output format of this function follows the following rules:

    • Each array element or object member is displayed on a separate line, indented by an additional level compared to its parent.
    • Each indent level adds two leading spaces.
    • Print a comma to separate a single array element or object member before separating the newline of two elements or members.
    • The key and value of an object member are separated by a colon, followed by a space (‘: ‘)。
    • Empty objects or arrays are printed on one line. There is no printing space between the left and right brackets.
    • Special characters in string scalars and key names use theJSON_QUOTE()Function uses the same rules for escape.
    mysql> SELECT JSON_PRETTY('123'); # scalar
    +--------------------+
    | JSON_PRETTY('123') |
    +--------------------+
    | 123                |
    +--------------------+
    
    mysql> SELECT JSON_PRETTY("[1,3,5]"); # array
    +------------------------+
    | JSON_PRETTY("[1,3,5]") |
    +------------------------+
    | [
      1,
      3,
      5
    ]      |
    +------------------------+
    
    mysql> SELECT JSON_PRETTY('{"a":"10","b":"15","x":"25"}'); # object
    +---------------------------------------------+
    | JSON_PRETTY('{"a":"10","b":"15","x":"25"}') |
    +---------------------------------------------+
    | {
      "a": "10",
      "b": "15",
      "x": "25"
    }   |
    +---------------------------------------------+
    
    mysql> SELECT JSON_PRETTY('["a",1,{"key1":
         >    "value1"},"5",     "77" ,
         >       {"key2":["value3","valueX",
         > "valueY"]},"j", "2"   ]')\G  # nested arrays and objects
    *************************** 1. row ***************************
    JSON_PRETTY('["a",1,{"key1":
                 "value1"},"5",     "77" ,
                    {"key2":["value3","valuex",
              "valuey"]},"j", "2"   ]'): [
      "a",
      1,
      {
        "key1": "value1"
      },
      "5",
      "77",
      {
        "key2": [
          "value3",
          "valuex",
          "valuey"
        ]
      },
      "j",
      "2"
    ]

    Add in MySQL 5.7.22.

  • JSON_STORAGE_SIZE(json_val)

    This function returns the number of bytes used to store the binary representation of the JSON document. When the parameter isJSONColumn, this is the space used to store JSON documents. json_ Val must be a valid JSON document or can be parsed as a string. If it’s a string, the function returns the amount of storage by parsing the string to JSON and converting it to binary to create a binary representation of JSON.NULLIf the parameter is, theNULL

    When JSON_ Val is notNULL, and is not or cannot be successfully resolved to a JSON document.

    To show that this function is in theJSONWe create ajtablecontainJSONcolumnjcolThen insert the JSON value into the table and use it to get the storage space used by the columnJSON_STORAGE_SIZE(), as follows:

    mysql> CREATE TABLE jtable (jcol JSON);
    Query OK, 0 rows affected (0.42 sec)
    
    mysql> INSERT INTO jtable VALUES
        ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> SELECT
        ->     jcol,
        ->     JSON_STORAGE_SIZE(jcol) AS Size
        -> FROM jtable;
    +-----------------------------------------------+------+
    | jcol                                          | Size |
    +-----------------------------------------------+------+
    | {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |
    +-----------------------------------------------+------+
    1 row in set (0.00 sec)

    Output based onJSON_STORAGE_SIZE(), the JSON document inserted into the column takes 47 bytes. When updated, the function displays the storage for the new setting value:

    mysql> UPDATE jtable
    mysql>     SET jcol = '{"a": 4.55, "b": "wxyz", "c": "[true, false]"}';
    Query OK, 1 row affected (0.04 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> SELECT
        ->     jcol,
        ->     JSON_STORAGE_SIZE(jcol) AS Size
        -> FROM jtable;
    +------------------------------------------------+------+
    | jcol                                           | Size |
    +------------------------------------------------+------+
    | {"a": 4.55, "b": "wxyz", "c": "[true, false]"} |   56 |
    +------------------------------------------------+------+
    1 row in set (0.00 sec)

    This function also displays the space currently used to store JSON documents in user variables:

    mysql> SET @j = '[100, "sakila", [1, 3, 5], 425.05]';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
    +------------------------------------+------+
    | @j                                 | Size |
    +------------------------------------+------+
    | [100, "sakila", [1, 3, 5], 425.05] |   45 |
    +------------------------------------+------+
    1 row in set (0.00 sec)
    
    mysql> SET @j = JSON_SET(@j, '$[1]', "json");
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
    +----------------------------------+------+
    | @j                               | Size |
    +----------------------------------+------+
    | [100, "json", [1, 3, 5], 425.05] |   43 |
    +----------------------------------+------+
    1 row in set (0.00 sec)
    
    mysql> SET @j = JSON_SET(@j, '$[2][0]', JSON_ARRAY(10, 20, 30));
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @j, JSON_STORAGE_SIZE(@j) AS Size;
    +---------------------------------------------+------+
    | @j                                          | Size |
    +---------------------------------------------+------+
    | [100, "json", [[10, 20, 30], 3, 5], 425.05] |   56 |
    +---------------------------------------------+------+
    1 row in set (0.00 sec)

    For JSON text, this function also returns the storage space currently used, as follows:

    mysql> SELECT
        ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
        ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
        ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C,
        ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;
    +----+----+----+----+
    | A  | B  | C  | D  |
    +----+----+----+----+
    | 45 | 44 | 47 | 56 |
    +----+----+----+----+
    1 row in set (0.00 sec)

    This function has been added in MySQL 5.7.22.