Movatterモバイル変換

JSON_CONTAINS(target,candidate[,path])

Indicates by returning 1 or 0 whether a givencandidate JSON document is contained within atarget JSON document, or—if apath argument was supplied—whether the candidate is found at a specific path within the target. ReturnsNULL if any argument isNULL, or if the path argument does not identify a section of the target document. An error occurs iftarget orcandidate is not a valid JSON document, or if thepath argument is not a valid path expression or contains a* or** wildcard.

To check only whether any data exists at the path, useJSON_CONTAINS_PATH() instead.

The following rules define containment:

Otherwise, the candidate value is not contained 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 a JSON document contains data at a given path or paths. ReturnsNULL if any argument isNULL. An error occurs if thejson_doc argument is not a valid JSON document, anypath argument is not a valid path expression, orone_or_all is not'one' or'all'.

To check for a specific value at a path, useJSON_CONTAINS() instead.

The return value is 0 if no specified path exists within the document. Otherwise, the return value depends on theone_or_all argument:

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 a JSON document, selected from the parts of the document matched by thepath arguments. ReturnsNULL if any argument isNULL or no paths locate a value in the document. An error occurs if thejson_doc argument is not a valid JSON document or anypath argument is not a valid path expression.

The return value consists of all values matched by thepath arguments. If it is possible that those arguments could return multiple values, the matched values are autowrapped as an array, in the order corresponding to the paths that produced them. Otherwise, the return value is the single matched 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 supports the-> operator as shorthand for this function as used with 2 arguments where the left hand side is aJSON column identifier (not an expression) and the right hand side is the JSON path to be matched within the column.

column->path

In MySQL 5.7.9 and later, the-> operator serves as an alias for theJSON_EXTRACT() function when used with two arguments, a column identifier on the left and a JSON path (a string literal) on the right that is evaluated against the JSON document (the column value). You can use such expressions in place of column references wherever they occur in SQL statements.

The twoSELECT 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 functionality is not limited toSELECT, as shown here:

mysql> ALTER TABLE jemp ADD COLUMN n INT;Query OK, 0 rows affected (0.68 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> UPDATE jemp SET n=1 WHERE c->"$.id" = "4";Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> 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)

(SeeIndexing a Generated Column to Provide a JSON Column Index, for the statements used to create and populate the table just shown.)

This also works with JSON array values, as shown here:

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. An expression using-> evaluates asNULL if no matching key is found in the target JSON document, as shown here:

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 behavior as seen in such cases when usingJSON_EXTRACT():

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, unquoting extraction operator available in MySQL 5.7.13 and later. Whereas the-> operator simply extracts a value, the->> operator in addition unquotes the extracted result. In other words, given aJSON column valuecolumn and a path expressionpath (a string literal), the following three expressions return the same value:

The->> operator can be used whereverJSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes (but is not limited to)SELECT lists,WHERE andHAVING clauses, andORDER BY andGROUP BY clauses.

The next few statements demonstrate some->> operator equivalences with other expressions in themysql 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)

SeeIndexing a Generated Column to Provide a JSON Column Index, for the SQL statements used to create and populate thejemp table in the set of examples just shown.

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

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: 0mysql> 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)

As with->, the->> operator is always expanded in the output ofEXPLAIN, as the following example demonstrates:

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: rangepossible_keys: i          key: i      key_len: 5          ref: NULL         rows: 2     filtered: 100.00        Extra: Using where1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* select#1 */ selectjson_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 how MySQL expands the-> operator in the same circumstances.

The->> operator was added in MySQL 5.7.13.

JSON_KEYS(json_doc[,path])

Returns the keys from the top-level value of a JSON object as a JSON array, or, if apath argument is given, the top-level keys from the selected path. ReturnsNULL if any argument isNULL, thejson_doc argument is not an object, orpath, if given, does not locate an object. An error occurs if thejson_doc argument is not a valid JSON document or thepath argument is not a valid path expression or contains a* or** wildcard.

The result array is empty if the selected object is empty. If the top-level value has nested subobjects, the return value does not include keys 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 to the given string within a JSON document. ReturnsNULL if any of thejson_doc,search_str, orpath arguments areNULL; nopath exists within the document; orsearch_str is not found. An error occurs if thejson_doc argument is not a valid JSON document, anypath argument is not a valid path expression,one_or_all is not'one' or'all', orescape_char is not a constant expression.

Theone_or_all argument affects the search as follows:

Within thesearch_str search string argument, the% and_ characters work as for theLIKE operator:% matches any number of characters (including zero characters), and_ matches exactly one character.

To specify a literal% or_ character in the search string, precede it by the escape character. The default is\ if theescape_char argument is missing orNULL. Otherwise,escape_char must be a constant that is empty or one character.

For more information about matching and escape character behavior, see the description ofLIKE inSection 12.8.1, “String Comparison Functions and Operators”. For escape character handling, a difference from theLIKE behavior is that the escape character forJSON_SEARCH() must evaluate to a constant at compile time, not just at execution time. For example, ifJSON_SEARCH() is used in a prepared statement and theescape_char argument is supplied using a? parameter, the parameter value might be constant at execution time, but is not at compile time.

search_str andpath are always interpeted as utf8mb4 strings, regardless of their actual encoding. This is a known issue which is fixed in MySQL 8.0 ( Bug #32449181).

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 JSON path syntax supported by MySQL, including rules governing the wildcard operators* and**, seeJSON Path Syntax.