# BSON\_MATCH\_ANY and BSON\_MATCH\_ANY\_EXISTS Checks for the existence of data within a BSON document that matches specified filter conditions. Is more succinct than extracting values and checking them using SQL filters. ## 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(, , ) ``` * 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(, ) ``` ## 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 `` 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(::?[, ]) ``` ## Arguments * ``: 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_` * `BSON_NORMALIZE()` * `MATCH_PARAM__STRICT()` functions, which behave similar to the corresponding `BSON_EXTRACT_` function. Refer to [BSON Functions](https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions.md) 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 ``, the built-in evaluates as SQL `NULL`. * `MATCH_PARAM__STRICT()` will evaluate as a SQL `NULL` if there is not a match to `` 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](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-match-any.md) 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](https://docs.singlestore.com/cloud/reference/sql-reference/json-functions/json-match-any.md) 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; ``` ```output +--------+ | Result | +--------+ | 1 | +--------+ ``` ```sql SELECT BSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 10, '[{"a":10}]':>BSON, 'a') AS Result; ``` ```output +--------+ | 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; ``` ```output +--------+ | Result | +--------+ | 1 | +--------+ ``` ```sql SELECT BSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() = "abc",@bsonObj,'a','x') AS Result; ``` ```output +--------+ | Result | +--------+ | 1 | +--------+ ``` ```sql SELECT BSON_MATCH_ANY_EXISTS(@bsonObj, 'a', 'y', 'key2') AS Result; ``` ```output +--------+ | Result | +--------+ | 1 | +--------+ ``` *** Modified at: November 11, 2024 Source: [/cloud/reference/sql-reference/bson-functions/bson-match-any-and-bson-match-any-exists/](https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions/bson-match-any-and-bson-match-any-exists/) (An index of the documentation is available at /llms.txt)