Rechercher dans le manuel MySQL
12.17.3 Functions That Search JSON Values
The functions in this section perform search operations on JSON values to extract data from them, report whether data exists at a location within them, or report the path to data within them.
JSON_CONTAINS(
target
,candidate
[,path
])Indicates by returning 1 or 0 whether a given
candidate
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, use
JSON_CONTAINS_PATH()
instead.The following rules define containment:
A candidate scalar is contained in a target scalar if and only if they are comparable and are equal. Two scalar values are comparable if they have the same
JSON_TYPE()
types, with the exception that values of typesINTEGER
andDECIMAL
are also comparable to each other.A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target.
A candidate nonarray is contained in a target array if and only if the candidate is contained in some element of the target.
A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key.
Otherwise, the candidate value is not contained in the target document.
- +-------------------------------+
- | JSON_CONTAINS(@j, @j2, '$.a') |
- +-------------------------------+
- | 1 |
- +-------------------------------+
- +-------------------------------+
- | JSON_CONTAINS(@j, @j2, '$.b') |
- +-------------------------------+
- | 0 |
- +-------------------------------+
- +-------------------------------+
- | JSON_CONTAINS(@j, @j2, '$.a') |
- +-------------------------------+
- | 0 |
- +-------------------------------+
- +-------------------------------+
- | 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. Returns
NULL
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, use
JSON_CONTAINS()
instead.The return value is 0 if no specified path exists within the document. Otherwise, the return value depends on the
one_or_all
argument:'one'
: 1 if at least one path exists within the document, 0 otherwise.'all'
: 1 if all paths exist within the document, 0 otherwise.
- +---------------------------------------------+
- | JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
- +---------------------------------------------+
- | 1 |
- +---------------------------------------------+
- +---------------------------------------------+
- | JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
- +---------------------------------------------+
- | 0 |
- +---------------------------------------------+
- +----------------------------------------+
- | JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
- +----------------------------------------+
- | 1 |
- +----------------------------------------+
- +----------------------------------------+
- | 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 the
path
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 the
path
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.- +--------------------------------------------+
- | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
- +--------------------------------------------+
- | 20 |
- +--------------------------------------------+
- +----------------------------------------------------+
- | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |
- +----------------------------------------------------+
- | [20, 10] |
- +----------------------------------------------------+
- +-----------------------------------------------+
- | JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |
- +-----------------------------------------------+
- | [30, 40] |
- +-----------------------------------------------+
MySQL 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.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 on the right that is evaluated against the JSON document (the column value). You can use such expressions in place of column identifiers wherever they occur in SQL statements.The two
SELECT
statements shown here produce the same output:- > FROM jemp
- +-------------------------------+-----------+------+
- | c | c->"$.id" | g |
- +-------------------------------+-----------+------+
- | {"id": "3", "name": "Barney"} | "3" | 3 |
- | {"id": "4", "name": "Betty"} | "4" | 4 |
- | {"id": "2", "name": "Wilma"} | "2" | 2 |
- +-------------------------------+-----------+------+
- > FROM jemp
- +-------------------------------+-----------+------+
- | c | c->"$.id" | g |
- +-------------------------------+-----------+------+
- | {"id": "3", "name": "Barney"} | "3" | 3 |
- | {"id": "4", "name": "Betty"} | "4" | 4 |
- | {"id": "2", "name": "Wilma"} | "2" | 2 |
- +-------------------------------+-----------+------+
This functionality is not limited to
SELECT
, as shown here:- Query OK, 0 rows affected (0.68 sec)
- Query OK, 1 row affected (0.04 sec)
- > FROM jemp
- +-------------------------------+-----------+------+------+
- | c | c->"$.id" | g | n |
- +-------------------------------+-----------+------+------+
- | {"id": "4", "name": "Betty"} | "4" | 4 | 1 |
- +-------------------------------+-----------+------+------+
- Query OK, 1 row affected (0.04 sec)
- > FROM jemp
- +-------------------------------+-----------+------+------+
- | c | c->"$.id" | g | n |
- +-------------------------------+-----------+------+------+
- +-------------------------------+-----------+------+------+
(See Indexing 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:
- Query OK, 0 rows affected (0.26 sec)
- Query OK, 1 row affected (0.04 sec)
- +--------------+
- | a->"$[4]" |
- +--------------+
- | 44 |
- | [22, 44, 66] |
- +--------------+
- +------------------------------+------+
- | a | b |
- +------------------------------+------+
- | [3, 10, 5, 17, 44] | 33 |
- | [3, 10, 5, 17, [22, 44, 66]] | 0 |
- +------------------------------+------+
Nested arrays are supported. An expression using
->
evaluates asNULL
if no matching key is found in the target JSON document, as shown here:- +------------------------------+------+
- | a | b |
- +------------------------------+------+
- | [3, 10, 5, 17, [22, 44, 66]] | 0 |
- +------------------------------+------+
- +--------------+
- | a->"$[4][1]" |
- +--------------+
- | 44 |
- +--------------+
This is the same behavior as seen in such cases when using
JSON_EXTRACT()
:This is an improved, unquoting extraction operator. 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
, the following three expressions return the same value:JSON_UNQUOTE(
column
->
path
)column
->>path
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 the mysql client:- +-------------------------------+------+
- | c | g |
- +-------------------------------+------+
- | {"id": "3", "name": "Barney"} | 3 |
- | {"id": "4", "name": "Betty"} | 4 |
- +-------------------------------+------+
- +----------+
- | name |
- +----------+
- | "Barney" |
- | "Betty" |
- +----------+
- +--------+
- | name |
- +--------+
- | Barney |
- | Betty |
- +--------+
- +--------+
- | name |
- +--------+
- | Barney |
- | Betty |
- +--------+
See Indexing a Generated Column to Provide a JSON Column Index, for the SQL statements used to create and populate the
jemp
table in the set of examples just shown.This operator can also be used with JSON arrays, as shown here:
- Query OK, 0 rows affected (0.26 sec)
- -> ('[3,10,5,"x",44]', 33),
- -> ('[3,10,5,17,[22,"y",66]]', 0);
- Query OK, 2 rows affected (0.04 sec)
- +-----------+--------------+
- | a->"$[3]" | a->"$[4][1]" |
- +-----------+--------------+
- | 17 | "y" |
- +-----------+--------------+
- +------------+---------------+
- | a->>"$[3]" | a->>"$[4][1]" |
- +------------+---------------+
- +------------+---------------+
As with
->
, the->>
operator is always expanded in the output ofEXPLAIN
, as the following example demonstrates:- *************************** 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
- *************************** 1. row ***************************
- Level: Note
- Code: 1003
- Message: /* select#1 */ select
This is similar to how MySQL expands the
->
operator in the same circumstances.Returns the keys from the top-level value of a JSON object as a JSON array, or, if a
path
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.
- +---------------------------------------+
- | JSON_KEYS('{"a": 1, "b": {"c": 30}}') |
- +---------------------------------------+
- | ["a", "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. Returns
NULL
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.The
one_or_all
argument affects the search as follows:'one'
: The search terminates after the first match and returns one path string. It is undefined which match is considered first.'all'
: The search returns all matching path strings such that no duplicate paths are included. If there are multiple strings, they are autowrapped as an array. The order of the array elements is undefined.
Within the
search_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 of
LIKE
in Section 12.5.1, “String Comparison Functions”. 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.- +-------------------------------+
- | JSON_SEARCH(@j, 'one', 'abc') |
- +-------------------------------+
- | "$[0]" |
- +-------------------------------+
- +-------------------------------+
- | JSON_SEARCH(@j, 'all', 'abc') |
- +-------------------------------+
- | ["$[0]", "$[2].x"] |
- +-------------------------------+
- +-------------------------------+
- | JSON_SEARCH(@j, 'all', 'ghi') |
- +-------------------------------+
- +-------------------------------+
- +------------------------------+
- | JSON_SEARCH(@j, 'all', '10') |
- +------------------------------+
- | "$[1][0].k" |
- +------------------------------+
- +-----------------------------------------+
- +-----------------------------------------+
- | "$[1][0].k" |
- +-----------------------------------------+
- +--------------------------------------------+
- +--------------------------------------------+
- | "$[1][0].k" |
- +--------------------------------------------+
- +---------------------------------------------+
- +---------------------------------------------+
- | "$[1][0].k" |
- +---------------------------------------------+
- +-------------------------------------------------+
- +-------------------------------------------------+
- | "$[1][0].k" |
- +-------------------------------------------------+
- +--------------------------------------------+
- +--------------------------------------------+
- | "$[1][0].k" |
- +--------------------------------------------+
- +-----------------------------------------------+
- +-----------------------------------------------+
- | "$[1][0].k" |
- +-----------------------------------------------+
- +---------------------------------------------+
- +---------------------------------------------+
- | "$[2].x" |
- +---------------------------------------------+
- +-------------------------------+
- | JSON_SEARCH(@j, 'all', '%a%') |
- +-------------------------------+
- | ["$[0]", "$[2].x"] |
- +-------------------------------+
- +-------------------------------+
- | JSON_SEARCH(@j, 'all', '%b%') |
- +-------------------------------+
- | ["$[0]", "$[2].x", "$[3].y"] |
- +-------------------------------+
- +---------------------------------------------+
- +---------------------------------------------+
- | "$[0]" |
- +---------------------------------------------+
- +---------------------------------------------+
- +---------------------------------------------+
- | "$[2].x" |
- +---------------------------------------------+
- +---------------------------------------------+
- +---------------------------------------------+
- +---------------------------------------------+
- +-------------------------------------------+
- | JSON_SEARCH(@j, 'all', '%b%', '', '$[1]') |
- +-------------------------------------------+
- +-------------------------------------------+
- +-------------------------------------------+
- | 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**
, see JSON Path Syntax.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-json-search-functions.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.