JSON_MATCH_ANY

Syntax

Longhand syntax:

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

  SQL

  Copy

  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.

  SQL

  Copy

  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).

• ::? indicates the following period-separated values are array/object neutral. For example, each key can either be a direct field of a JSON object or a field of a JSON object within an array.

• EXISTS: the optional <filter_predicate> is omitted in the shorthand version. So, the builtin will return true if the filter path exists (which is identical behavior as JSON_MATCH_ANY_EXISTS()).

  SQL

  Copy

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

Arguments

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

• filter_path: Each key in the filter path matches a nested document that can either be a direct child of a document or an element in a JSON array. The basic filter path existence rules are:

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

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

  • 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.

  • 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.

  • MATCH_PARAM_<type>_STRICT() will evaluate as a SQL NULL if there is not a match to <type> as defined below:

    • MATCH_PARAM_BIGINT_STRICT() matches only numeric JSON values (integers/floating point). Also, it rounds to the nearest integer.

    • MATCH_PARAM_BOOL_STRICT() matches only JSON booleans (true/false).

    • MATCH_PARAM_DOUBLE_STRICT() is used for all numeric types (integers/floats). 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() matches only JSON strings. 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.

  • MATCH_ELEMENTS when added to the JSON_MATCH_ANY function only the the array elements would be checked, not the entire array.

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE produce AS SELECT '{"apples" : { "bananas": {"limes": [{"oranges": "10"}, {"oranges": "342"}]}}}' :> JSON 
  AS prod_inv UNION SELECT '{"apples" : { "bananas": {"limes": [{"oranges": "158"}, {"oranges": null}]}}}' :> JSON;

SELECT * FROM produce 
WHERE JSON_MATCH_ANY(json_extract_string(MATCH_PARAM_JSON(), 'oranges') IS NULL, 
prod_inv,'apples','bananas','limes');

+-----------------------------------------------------------------------+
| prod_inv                                                              |
+-----------------------------------------------------------------------+
| {"apples":{"bananas":{"limes":[{"oranges":"158"},{"oranges":null}]}}} |
| {"apples":{"bananas":{"limes":[{"oranges":"10"},{"oranges":"342"}]}}} |
+-----------------------------------------------------------------------+

SELECT * FROM produce 
WHERE JSON_MATCH_ANY(json_extract_string(MATCH_PARAM_JSON(), 'oranges') IS NULL, 
prod_inv,'apples','bananas','limes' MATCH_ELEMENTS);

+-----------------------------------------------------------------------+
| prod_inv                                                              |
+-----------------------------------------------------------------------+
| {"apples":{"bananas":{"limes":[{"oranges":"158"},{"oranges":null}]}}} |
+-----------------------------------------------------------------------+

CREATE TABLE restaurants_json (
restaurantinfo JSON
);

INSERT INTO restaurants_json VALUES
('[{"city":"Paris",
    "type":"Bakery",
    "name":"La Boulangerie"},
   {"city":"Paris",
    "type":"Pizzeria",
    "name":"Pizza Pizza"}]'),
('[{"city":"Rome",
    "type":"Bakery",
    "name":"La Panetteria"},
   {"city":"Rome",
    "type":"Pizzeria",
    "name":"Roma Pizza"}]');

SELECT * 
FROM restaurants_json 
WHERE 
 JSON_MATCH_ANY(
    JSON_EXTRACT_STRING(MATCH_PARAM_JSON(),'type') = 'Bakery' 
    AND JSON_EXTRACT_STRING(MATCH_PARAM_JSON(),'name') = 'La Boulangerie', 
      restaurantinfo MATCH_ELEMENTS);

+------------------------------------------------------------------------------------------------------------------+
| restaurantinfo                                                                                                   |
+------------------------------------------------------------------------------------------------------------------+
| [{"city":"Paris","name":"La Boulangerie","type":"Bakery"},{"city":"Paris","name":"Pizza Pizza","type":"Pizzeria"}] |
+------------------------------------------------------------------------------------------------------------------+

SELECT * 
FROM restaurants_json 
WHERE 
 JSON_MATCH_ANY(
    JSON_EXTRACT_STRING(MATCH_PARAM_JSON(), 'type') = 'Bakery' 
    AND JSON_EXTRACT_STRING(MATCH_PARAM_JSON(), 'name') = 'Pizza Pizza',
     restaurantinfo MATCH_ELEMENTS);

Empty set (0.02 sec)

Columnstore Optimization

JSON_MATCH_ANY() is only optimized over columnstore if <filter_predicate> only contains:

• MATCH_PARAM_<type>() functions.

• Constant literals: strings, integers, floating point numbers, booleans, and NULL.

• Comparison operators: =, !=, <, <=, >, >=, <=>, IS, IN, and BETWEEN.

• Logical operators: AND, OR, and NOT.

• Typecasts: implicit and explicit.