Skip to main content

JSON_MATCH_ANY

Returns true if, in the JSON, there is a value at the specified filter path which evaluates the optional filter predicate as true.

If no filter predicate is provided, will return true if the filter path exists.

The filter path is a list of dictionary keys or zero-indexed array positions.

The basic rules of filter path existence are:

  • If the current element is a dictionary and the next key in the filter path matches a key in the dictionary.

  • If the current element is an array, either match the next key as a zero-indexed position in the array or as a key of any dictionary that is an element of the array. If a filter path key can either index the array or match a key in a dictionary element of the array JSON_MATCH_ANY() will match it as a zero-indexed position.

It is possible that there could be several values in a JSON which match a filter path. In this case the filter predicate is applied on all values until it evaluates to true.

Syntax

Longhand syntax:

  • JSON_MATCH_ANY returns true if there exists a value in the JSON at the filter path which evaluates the filter predicate to true.

    JSON_MATCH_ANY(<filter_predicate>, <json>, <filter_path>)
  • JSON_MATCH_ANY_EXISTS returns true if there is a value (possibly null) in the JSON at the filter path.

    JSON_MATCH_ANY_EXISTS(<json>, <filter_path>)

Shortened syntax:

  • filter_predicate is the same as in the longhand syntax.

  • filter_path is a dot-separated list of keys (as opposed to a comma-separated list of arguments in the longhand syntax).

    JSON_MATCH_ANY(<json>::?<filter_path>[, <filter_predicate>])

Arguments

  • json: the name of a JSON column or JSON variable.

  • filter_path: consult the filter path existence rules mentioned above.

    • The longhand syntax is a comma-separated list of dictionary keys or zero-indexed array positions.

    • The shorthand syntax is a period-separated list of dictionary keys or zero-indexed array positions.

  • filter_predicate: an optional predicate expression which is applied to every value matching the filter path.

    • Several special built-in functions are used within the filter predicate only. Every time the filter predicate is evaluated, these built-ins will be substituted with the corresponding value type in the filter path. If there is no matching type, then the built-in will evaluate as NULL.

    • Note the significant differences between these built-ins and JSON_EXTRACT_<type() built-ins (hence the STRICT postfix).

      • MATCH_PARAM_BIGINT_STRICT() is used for all numeric types. It performs rounding on floating point numbers. Unlike JSON_EXTRACT_BIGINT(), it will not convert non-numeric types (such as booleans, strings, JSON maps, and lists).

      • MATCH_PARAM_BOOL_STRICT() used for all boolean types (true and false).

      • MATCH_PARAM_DOUBLE_STRICT() is used for all numeric types. Unlike JSON_EXTRACT_DOUBLE(), it will not convert non-numeric types (such as booleans, strings JSON maps, and lists).

      • MATCH_PARAM_JSON() is used for all valid JavaScript types including true, false, JSON maps, and lists (there is intentionally no STRICT here).

      • MATCH_PARAM_STRING_STRICT() is used for all text types. Unlike JSON_EXTRACT_STRING() it will not convert non-string types (not enclosed by quotes). Has same behavior as JSON_EXTRACT_STRING() with regards to json_extract_string_collation global variable.

Return Type

  • If no filter predicate is provided, returns true if the filter path exists in the JSON.

  • If a filter predicate is provided, it will return as true if there is a value at the JSON filter path that evaluates the filter predicate to true.

Examples

The examples in this section use the the table and values shown below:

CREATE TABLE json_exp(id int, json_row JSON);

INSERT INTO json_exp VALUES(1, '{"alpha": {"beta": 6}}'), (2, '{"alpha": {"beta": 4}}'), (3, '{"alpha": [{"beta": 3}, {"beta": 1}, {"gamma": 2}, {"beta": 6}]}'),
(4, '{"alpha": {"beta": [1,2,3,4,5,6]}}'), (5, '{"alpha": 1, "beta": 1}'), (6, '{"alpha": {"beta": null}}'), (7, '{"alpha": {"beta": true}}'), (8, '{"alpha": {"beta": "6"}}'),
(9, '{"alpha": [false, false, {"1": {"beta": 1}}]}');

SELECT * FROM json_exp;

****
+---------------------------------------------------------------+
|id  | json_row                                                 |
+----+----------------------------------------------------------+
| 1  | {"alpha":{"beta":6}}                                     |
| 2  | {"alpha":{"beta":4}}                                     |
| 3  | {"alpha":[{"beta":3},{"beta":1},{"gamma":2},{"beta":6}]} |
| 4  | {"alpha":{"beta":[1,2,3,4,5,6]}}                         |
| 5  | {"alpha":1,"beta":1}                                     |
| 6  | {"alpha":{"beta":null}}                                  |
| 7  | {"alpha":{"beta":true}}                                  |
| 8  | {"alpha":{"beta":"6"}}                                   |
| 9  | {"alpha":[false,false,{"1":{"beta":1}}]}                 |
+----+----------------------------------------------------------+

Example 1

JSON_MATCH_ANY_EXISTS returns true for {"alpha”: [{"beta”: 3}, {"beta”: 1}, {“gamma: 2}, {"beta”: 6}]} because beta is matched to a key of a dictionary that is an element in the array.

Longhand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY_EXISTS(json_row, 'alpha', 'beta');

Shorthand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.beta);

Either the version of the function will yield the same answer.

****
+----+
| id |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
| 6  |
| 7  |
| 8  |
+----+

Example 2

  • For {"alpha”: [{"beta”: 3}, {"beta”: 1}, {“gamma”: 2}, {"beta”: 6}]} the filter predicate is evaluated on three, one, and six. Six evaluates the filter predicate to true.

  • For {"alpha”: {"beta”: [1,2,3,4,5,6]}} the filter predicate is evaluated on all values in the array. Six evaluates the filter predicate to true.

  • For {"alpha”: {"beta”: "6"}} the filter predicate is evaluated on six. This is because a JSON string MATCH_PARAM_DOUBLE_STRICT() evaluates as NULL and the filter predicate returns false. This is distinctly different from how JSON_EXTRACT_DOUBLE() works.

Longhand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(MATCH_PARAM_DOUBLE_STRICT() = 6, json_row, 'alpha', 'beta');

Shorthand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(MATCH_PARAM_DOUBLE_STRICT() = 6, json_row, 'alpha', 'beta');

Result for both function versions:

****
+----+
| id |
+----+
| 1  |
| 3  |
| 4  |
+----+

Example 3

MATCH_PARAM_JSON() extracts null as a JSON null.

Longhand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(JSON_GET_TYPE(MATCH_PARAM_JSON()) = 'null', json_row, 'alpha', 'beta');

Shorthand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.beta, JSON_GET_TYPE(MATCH_PARAM_JSON()) = 'null');

Result for both function versions:

****
+----+
| id |
+----+
| 6  |
+----+

Example 4

As described by the filter path existence rules, “If a filter path key can either index the array or match a key in a dictionary element of the array,  JSON_MATCH_ANY() will match it as a zero-indexed position.” Therefore, {"a": [false, false, {"1": {"b": 1}}]} does not match because filter path key 1 can index the array but at index 1 is the value false.

Longhand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 1, json_row, 'alpha’, 1, 'beta’);

Shorthand:

SELECT id FROM json_exp WHERE JSON_MATCH_ANY(json_row::?alpha.`1`.beta, MATCH_PARAM_BIGINT_STRICT() = 1);

Result for both function versions:

****
+----+
| id |
+----+
| 3  |
+----+