12.17.3 Functions That Search JSON Values

JSON_CONTAINS(target, candidate[, path])

Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document, or—if a path argument was supplied—whether the candidate is found at a specific path within the target. Returns NULL if any argument is NULL, or if the path argument does not identify a section of the target document. An error occurs if target or candidate is not a valid JSON document, or if the path 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:

Otherwise, the candidate value is not contained in the target document.

Starting with MySQL 8.0.17, queries using JSON_CONTAINS() on InnoDB tables can be optimized using multi-valued indexes; see Multi-Valued Indexes, for more information.

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 is NULL. An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, or one_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:

JSON_EXTRACT(json_doc, path[, path] ...)

Returns data from a JSON document, selected from the parts of the document matched by the path arguments. Returns NULL if any argument is NULL or no paths locate a value in the document. An error occurs if the json_doc argument is not a valid JSON document or any path 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.

MySQL supports the -> operator as shorthand for this function as used with 2 arguments where the left hand side is a JSON column identifier (not an expression) and the right hand side is the JSON path to be matched within the column.

column->path

The -> operator serves as an alias for the JSON_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:

This functionality is not limited to SELECT, as shown here:

(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:

Nested arrays are supported. An expression using -> evaluates as NULL if no matching key is found in the target JSON document, as shown here:

This is the same behavior as seen in such cases when using JSON_EXTRACT():

column->>path

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 a JSON column value column and a path expression path, the following three expressions return the same value:

The ->> operator can be used wherever JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This includes (but is not limited to) SELECT lists, WHERE and HAVING clauses, and ORDER BY and GROUP BY clauses.

The next few statements demonstrate some ->> operator equivalences with other expressions in the mysql client:

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:

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

This is similar to how MySQL expands the -> operator in the same circumstances.

JSON_KEYS(json_doc[, path])

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. Returns NULL if any argument is NULL, the json_doc argument is not an object, or path, if given, does not locate an object. An error occurs if the json_doc argument is not a valid JSON document or the path argument is not a valid path expression or contains a * or ** wildcard.

The 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_OVERLAPS(json_doc1, json_doc2)

Compares two JSON documents. Returns true (1) if the two document have any key-value pairs or array elements in common. If both arguments are scalars, the function performs a simple equality test.

This function serves as counterpart to JSON_CONTAINS(), which requires all elements of the array searched for to be present in the array searched in. Thus, JSON_CONTAINS() performs an AND operation on search keys, while JSON_OVERLAPS() performs an OR operation.

Queries on JSON columns of InnoDB tables using JSON_OVERLAPS() in the WHERE clause can be optimized using multi-valued indexes. Multi-Valued Indexes, provides detailed information and examples.

When two comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not:

Partial matches are treated as no match, as shown here:

When comparing objects, the result is true if they have at least one key-value pair in common.

If two scalars are used as the arguments to the function, JSON_OVERLAPS() performs a simple test for equality:

When comparing a scalar with an array, JSON_OVERLAPS() attempts to treat the scalar as an array element. In this example, the second argument 6 is interpreted as [6], as shown here:

The function does not perform type conversions:

JSON_OVERLAPS() was added in MySQL 8.0.17.

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 the json_doc, search_str, or path arguments are NULL; no path exists within the document; or search_str is not found. An error occurs if the json_doc argument is not a valid JSON document, any path argument is not a valid path expression, one_or_all is not 'one' or 'all', or escape_char is not a constant expression.

The one_or_all argument affects the search as follows:

Within the search_str search string argument, the % and _ characters work as for the LIKE 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 the escape_char argument is missing or NULL. 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 and Operators”. For escape character handling, a difference from the LIKE behavior is that the escape character for JSON_SEARCH() must evaluate to a constant at compile time, not just at execution time. For example, if JSON_SEARCH() is used in a prepared statement and the escape_char argument is supplied using a ? parameter, the parameter value might be constant at execution time, but is not at compile time.

For more information about the JSON path syntax supported by MySQL, including rules governing the wildcard operators * and **, see JSON Path Syntax.

value MEMBER OF(json_array)

Returns true (1) if value is an element of json_array, otherwise returns false (0). value must be a scalar or a JSON document; if it is a scalar, the operator attempts to treat it as an element of a JSON array.

Queries using MEMBER OF() on JSON columns of InnoDB tables in the WHERE clause can be optimized using multi-valued indexes. See Multi-Valued Indexes, for detailed information and examples.

Simple scalars are treated as array values, as shown here:

Partial matches of array element values do not match:

Conversions to and from string types are not performed:

To use this operator with a value which itself an array, it is necessary to cast it explicitly as a JSON array. You can do this with CAST(... AS JSON):

It is also possible to perform the necessary cast using the JSON_ARRAY() function, like this:

Any JSON objects used as values to be tested or which appear in the target array must be coerced to the correct type using CAST(... AS JSON) or JSON_OBJECT(). In addition, a target array containing JSON objects must itself be cast using JSON_ARRAY. This is demonstrated in the following sequence of statements:

The MEMBER OF() operator was added in MySQL 8.0.17.