JSON_ MATCH_ ANY
On this page
This function can find data within a JSON document that matches the specified filter conditions.
Syntax
Longhand syntax:
-
JSON_
returns true if there exists a value in the JSON at the filter path for which the filter predicate evaluates to true.MATCH_ ANY JSON_MATCH_ANY(<filter_predicate>, <json>, <filter_path>) -
JSON_
returns true if there is a value (possibly null) in the JSON at the filter path.MATCH_ ANY_ EXISTS JSON_MATCH_ANY_EXISTS(<json>, <filter_path>)
Shortened syntax:
-
filter_
is the same as in the longhand syntax.predicate -
filter_
is a dot-separated list of keys (as opposed to a comma-separated list of arguments in the longhand syntax).path -
::?
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_
is omitted in the shorthand version.predicate> So, the builtin will return true if the filter path exists (which is identical behavior as JSON_
).MATCH_ ANY_ EXISTS() JSON_MATCH_ANY(<json>::?<filter_path>[, <filter_predicate>])
Arguments
-
json
: the name of a JSON column or JSON variable. -
filter_
: 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.path 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_
will match it as a zero-indexed position.MATCH_ ANY() -
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_
: an optional predicate expression which is applied to every value matching the filter path.predicate -
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_
will evaluate as a SQL NULL if there is not a match toPARAM_ <type>_ STRICT() <type>
as defined below:-
MATCH_
matches only numeric JSON values (integers/floating point).PARAM_ BIGINT_ STRICT() Also, it rounds to the nearest integer. -
MATCH_
matches only JSON booleans (true/false).PARAM_ BOOL_ STRICT() -
MATCH_
is used for all numeric types (integers/floats).PARAM_ DOUBLE_ STRICT() Unlike JSON_
, it will not convert non-numeric types (such as booleans, strings JSON maps, and lists).EXTRACT_ DOUBLE() -
MATCH_
is used for all valid JavaScript types including true, false, JSON maps, and lists (there is intentionally no STRICT here).PARAM_ JSON() -
MATCH_
matches only JSON strings.PARAM_ STRING_ STRICT() Unlike JSON_
it will not convert non-string types (not enclosed by quotes).EXTRACT_ STRING() Has same behavior as JSON_
with regards to json_EXTRACT_ STRING() 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_
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_
evaluates asPARAM_ DOUBLE_ STRICT() NULL
and the filter predicate returns false.This is distinctly different from how JSON_
works.EXTRACT_ DOUBLE()
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(json_row::?alpha.beta, MATCH_PARAM_DOUBLE_STRICT() = 6);
Result for both function versions:
+----+
| id |
+----+
| 1 |
| 3 |
| 4 |
+----+
Example 3
MATCH_
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_
will match it as a zero-indexed position.
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 |
+----+
Columnstore Optimization
JSON_
-
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.
Last modified: April 20, 2023