Skip to main content

SingleStoreDB v8.0

JSON_MATCH_ANY

This function can find data within a JSON document that matches the specified filter conditions. It is more succinct than extracting values and checking them using regular SQL filters.

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.

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

  • ::? 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()).

    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.

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(json_row::?alpha.beta, MATCH_PARAM_DOUBLE_STRICT() = 6);

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, {"alpha": [false, false, {"1": {"beta": 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  |
+----+

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.

In this section: