JSON_MATCH_ANY

Longhand Syntax

• The JSON_MATCH_ANY function returns true if there exists a value in the JSON at the filter path for which the filter predicate evaluates to true. The filter predicate and filter path are described in Arguments.

  SQL

  Copy

  JSON_MATCH_ANY(<filter_predicate>, <json>, <filter_path>)

• The JSON_MATCH_ANY_EXISTS  function 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>)

Shorthand 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: As the optional <filter_predicate> is omitted in the EXISTS function, the syntax and behavior of the EXISTS function is identical whether using either the shorthand or longhand syntax.

  SQL

  Copy

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

Filter Path

• The filter_path, which is a set of keys or zero-indexed array positions, is used to recursively extract a JSON value or an element of a JSON array from the json argument.

• If no filter_predicate is provided, true is returned if the filter path exists.

• The filter_path is evaluated recursively against the JSON in the json column using the following rules.

  • If the current element identified by the filter_path is an object, the next key in the filter_path is matched against the keys in the object. 

  • If the current element identified by the filter_path is an array, the next key in the filter_path is either matched as a zero-indexed position in the array or against the keys of any object that is an element of the array. If both a zero-indexed array match or a key match are possible, the zero-indexed array match will be used.

  • It is possible that there are several values in a json value which match the filter_path. In this case, the filter_predicate is applied on all values. The function returns true if the predicate evalutates to true on one of the values.

Filter Predicate

• The filter_predicate is an optional predicate expression which is applied to every value matching the filter_path.

• Several special built-in functions are used for the filter_predicate. The built-in functions that can be used in the filter_predicate  include:

  • JSON_GET_TYPE

  • JSON_EXTRACT_<type>

  • MATCH_PARAM_<type>_STRICT functions, as described below.

  • MATCH_PARAM_JSON() is used for all valid JSON types including true, false, JSON objects, and JSON arrays. There is intentionally no STRICT here.

• The filter_predicate is evaluated by running the specified built-in function on the value identified by the filter_path. If the value at the filter_path does not match the <type>, the built-in evaluates as SQL-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). Rounds to the nearest integer.

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

  • MATCH_PARAM_DOUBLE_STRICT() matches all numeric types (integers/floats). Unlike JSON_EXTRACT_DOUBLE(), it will not convert non-numeric types (booleans, strings, JSON objects, and JSON arrays) to double values.

  • MATCH_PARAM_STRING_STRICT() matches only JSON strings. Unlike JSON_EXTRACT_STRING() it will not convert non-string types (not enclosed by quotes). Behaves the same as JSON_EXTRACT_STRING() with regards to the json_extract_string_collation global variable.

• MATCH_ELEMENTS when added to the JSON_MATCH_ANY function, matches only the array elements themselves, not the entire array.

Example 1 - JSON_MATCH_ANY_EXISTS

Use the JSON_MATCH_ANY_EXISTS() function to find rows that contain the path alpha.beta ('alpha,'beta' in longhand). The ORDER BY clause and is included to ensure consistent results.

Longhand Syntax:

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

Shorthand Syntax:

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

Both versions of the query produce the following result.

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

The JSON_MATCH_ANY_EXISTS function returns true for the row with id 3,  {"alpha”: [{"beta”: 3}, {"beta”: 1}, {“gamma: 2}, {"beta”: 6}]} , because beta is matched to a key of an object that is an element in the array. Thus the row with id 3 is included in the result.

The row with id 6, {"alpha":{"beta":null}}, is included in the result because the path alpha.beta ('alpha','beta' in longhand) exists in the JSON in that row, the fact that the value at that path is null does not prevent the row from being included in the result.

Example 2 - MATCH_PARAM_DOUBLE_STRICT

Consider the following query that uses MATCH_PARAM_DOUBLE_STRICT() to looks for the value 6 at the path alpha.beta ('alpha','beta' in longhand).

Longhand Syntax:

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

Shorthand Syntax:

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

Both versions of the query produce the following result.

+---------------------------------------------------------------+
|id  | json_row                                                 |
+----+----------------------------------------------------------+
| 1  | {"alpha":{"beta":6}}                                     |
| 3  | {"alpha":[{"beta":3},{"beta":8},{"gamma":2},{"beta":6}]} |
| 4  | {"alpha":{"beta":[1,2,3,4,5,6]}}                         |
+----+----------------------------------------------------------+

For the row with id 3,  {"alpha”: [{"beta”: 3}, {"beta”: 1}, {“gamma”: 2}, {"beta”: 6}]}, the filter_path matches to the objects with values 3, 1, and 6 and the filter_predicate is evaluated on those values. The value 6 evaluates to true, and the row is included in the result.

For the row with id 4, {"alpha”: {"beta”: [1,2,3,4,5,6]}}, the filter_predicate is evaluated on all values in the array. The value 6 evaluates to true, and the row is included in the result.

For the row with id 8, {"alpha”: {"beta”: "6"}}, the filter_predicate is evaluated on the JSON string value "6", which causes MATCH_PARAM_DOUBLE_STRICT() to evaluate to SQL-NULL (distinct from JSON null) because of mismatching types. Thus, the filter predicate evaluates to false and this row is not included in the result. This is distinctly different from how JSON_EXTRACT_DOUBLE() works.

Example 3 - MATCH_PARAM_JSON

Use the MATCH_PARAM_JSON() function to identify null values at the path alpha.beta ('alpha','beta' in longhand). The function MATCH_PARAM_JSON() extracts null as JSON null.

Longhand Syntax:

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

Shorthand Syntax:

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

Both versions of the query produce the following result.

+---------------------------------------------------------------+
|id  | json_row                                                 |
+----+----------------------------------------------------------+
| 6  | {"alpha":{"beta":null}}                                  |
+----+----------------------------------------------------------+

Example 4 - MATCH_PARAM_BIGINT_STRICT

Use the MATCH_PARAM_BIGINT_STRICT() function to extract numeric values at the path alpha.1.beta ('alpha','1', 'beta') that round to 1.

Longhand Syntax:

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

Shorthand Syntax:

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

Both versions of the query produce the following result.

+---------------------------------------------------------------+
|id  | json_row                                                 |
+----+----------------------------------------------------------+
| 3  | {"alpha":[{"beta":3},{"beta":8},{"gamma":2},{"beta":6}]} |
+----+----------------------------------------------------------+

If a key in the filter_path can either index the array or match a key in an object in the array,  JSON_MATCH_ANY() will match the key as a zero-indexed array position. Therefore, the row with id 9, {"alpha": ["zeta", "zeta", {"1": {"beta": 8}}]}, does not match because the key 1 in the filter_path ( alpha.1.beta) can index the array but the value at index 1 is "zeta", which is not equal to 8 and fails the filter_predicate test.

Example 5 - MATCH ELEMENTS

Use the MATCH_ELEMENTS keyword to test a predicate directly against all elements in an array without first testing against the whole array.

Create a purchase_info table with a column, purchases of type JSON, and insert the following data into this table.

CREATE TABLE purchase_info(p JSON);

INSERT INTO purchase_info VALUES
    ('{"purchase": {"items": [1,2,3], "name": "John"}, "order_id": 101}'),
    ('{"purchase": {"items": 1, "name": "Dave"}, "order_id": 102}'),
    ('{"purchase": {"items": [1], "name": "Ann"}, "order_id": 103}'),
    ('{"purchase": {"items": [2,3,4], "name": "Brian"}, "order_id": 104}');

The first of the following two queries looks for purchases where item 1 was ordered. This query looks for a match for the value 1 either in the array of items (order 101) or in the value of items (order 102).

However, in many cases, the desired behavior is to search only the elements of the array. The second query adds MATCH_ELEMENTS after the purchase.items filter_path to specify that only the elements of the array should be searched.

-- without MATCH_ELEMENTS
SELECT
    p::%order_id AS order_id,
    JSON_MATCH_ANY(p::?purchase.items, MATCH_PARAM_BIGINT_STRICT() = 1) AS ordered_item_one
FROM purchase_info
ORDER BY order_id;

-- with MATCH_ELEMENTS
SELECT
    p::%order_id AS order_id,
    JSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 1, p, "purchase", "items" MATCH_ELEMENTS) AS ordered_item_one_in_array
FROM purchase_info
ORDER BY order_id;

The result of the first SELECT query (without MATCH_ELEMENTS) is shown below. Orders 101, 102, and 103 match the filter_predicate because 1 is in the value of items in order 102 and in the array of items in order 101 and 103. This query demonstrates how the entire value of items, plus the individual values in the items array are searched.

+----------+------------------+
| order_id | ordered_item_one |
+----------+------------------+
|      101 |                1 |
|      102 |                1 |
|      103 |                1 |
|      104 |                0 |
+----------+------------------+

The result of the second SELECT query (with * to indicate MATCH_ELEMENTS) only orders 101 and 103 match the filter_predicate and the query is more efficient as the system skips matching the value of the items array iteslf.

+----------+---------------------------+
| order_id | ordered_item_one_in_array |
+----------+---------------------------+
|      101 |                         1 |
|      102 |                         0 |
|      103 |                         1 |
|      104 |                         0 |
+----------+---------------------------+

The second query expressed with shorthand syntax is as follows:

SELECT
    p::%order_id AS order_id,
    JSON_MATCH_ANY(p::?purchase.items*, MATCH_PARAM_BIGINT_STRICT() = 1) AS ordered_item_one_in_array
FROM purchase_info;

Example 6 - Match Multiple Paths

With JSON_MATCH_ANY, it is possible to match multiple paths in a single JSON value.

Create a restaurants table with a column of type JSON which will contain information about restaurants. Insert two rows into that table.

Each row added to the table is a JSON array and each row represents information about restaurants for a particular city.

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"}]');

It is possible to query the JSON arrays in the restaurantinfo column to match on both the type and name of the restaurant.

The query below searches for restaurant information for cities that have a bakery named La Boulangerie. The row with restaurants in Paris matches as there is a bakery named La Boulangerie in Paris.

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

The query below looks for restaurant information for cities that have a bakery named Pizza Pizza. There are no matches and no results are returned.

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)

A query, such as the above two queries, which use a predicate (WHERE clause) that contains JSON_MATCH_ANY() with a nested JSON_EXTRACT_<type>() over MATCH_PARAM_JSON() will result in a more efficient query. In this type of query only the columnstore segments for the keys type and name are decoded, which reduces the amount of data that is read and processed.