BSON_MATCH_ANY and BSON_MATCH_ANY_EXISTS

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

Longhand Syntax

The BSON_MATCH_ANY function returns 1 if there exists a value in the BSON at the filter path for which the filter predicate evaluates to true.
SQL
```
BSON_MATCH_ANY(<filter_predicate>, <bson>, <filter_path>)
```
The BSON_MATCH_ANY_EXISTS function returns true if there is a value (possibly null) in the BSON at the filter path.
SQL
```
BSON_MATCH_ANY_EXISTS(<bson>, <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 BSON object or a field of a BSON 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
```
BSON_MATCH_ANY(<bson>::?<filter_path>[, <filter_predicate>])
```

Arguments

<bson>: the name of a BSON column or a BSON value.
filter_path: a path specification.
- Longhand syntax: a comma-separated list of dictionary keys or zero-indexed array positions.
- Shorthand syntax: 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. Consists of special built-in functions.

Filter Path

The filter_path, which is a set of keys or zero-indexed array positions, is used to recursively extract a BSON value or an element of a BSON array from the bson argument.
If no filter_predicate is provided, true is returned if the filter path exists.
The filter_path is evaluated recursively against the BSON in the bson 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 bson 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:
- BSON_EXTRACT_<type>
- BSON_NORMALIZE()
- MATCH_PARAM_<type>_STRICT() functions, which behave similar to the corresponding BSON_EXTRACT_<type> function. Refer to BSON Functions in addition to the descriptions below for more information.
- MATCH_PARAM_BSON() is used for all valid BSON types including true, false, BSON objects, and BSON 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 BSON values (integers/floating point). Rounds to the nearest integer.
- MATCH_PARAM_BOOL_STRICT() matches only BSON booleans (true/false).
- MATCH_PARAM_DOUBLE_STRICT() matches all numeric types (integers/floats). Unlike BSON_EXTRACT_DOUBLE(), it will not convert non-numeric types (booleans, strings, BSON objects, and BSON arrays) to double values.
- MATCH_PARAM_STRING_STRICT() matches only BSON strings. Unlike BSON_EXTRACT_STRING() it will not convert non-string types (not enclosed by quotes). Collation is controlled by the json_extract_string_collation global variable.
- MATCH_PARAM_DATETIME_STRICT() matches only BSON datetime types.
- MATCH_PARAM_BINARY_STRICT() matches only BSON binary types.
MATCH_ELEMENTS when added to the BSON_MATCH_ANY function, matches only the array elements themselves, not the entire array.

Note

Refer to JSON_MATCH_ANY for information on MATCH_ELEMENTS and examples of the shorthand syntax.

Return Type

Return type is TINYINT.
Returns 1 or 0 to indicate true or false.

Return Value

If a filter_predicate is provided, returns 1 if the predicate evaulates to true for any value found at the filter_path.
If a filter_predicate is not provided, returns 1 if the filter_path exists in the BSON.
Returns 0 otherwise.

Examples

Refer to JSON_MATCH_ANY for additional examples.

Usage of filter_path is shown in the following two queries, both of which return 1:

SQL

SELECT BSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 10, '[{"a":10}]':>BSON, '0', 'a') AS Result;

+--------+
| Result |
+--------+
|      1 |
+--------+

SQL

SELECT BSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 10, '[{"a":10}]':>BSON, 'a')  AS Result;

+--------+
| Result |
+--------+
|      1 |
+--------+

The following examples in this section use this BSON object:

SQL

SELECT '{ 
  "a": [
   { "x": "abc",
     "y": {
         "key1": true,
         "key2": false }},
   { "m": "cde",
     "n": 234 },
   { "p": true },
   { "q": null }],
  "b": true}':>BSON INTO @bsonObj;

Here are some examples:

SQL

SELECT BSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 234,@bsonObj,'a','n') AS Result;

+--------+
| Result |
+--------+
|      1 |
+--------+

SQL

SELECT BSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() = "abc",@bsonObj,'a','x') AS Result;

+--------+
| Result |
+--------+
|      1 |
+--------+

SQL

SELECT BSON_MATCH_ANY_EXISTS(@bsonObj, 'a', 'y', 'key2') AS Result;

+--------+
| Result |
+--------+
|      1 |
+--------+