Sign inTry Free

BSON_MATCH_ANY and BSON_MATCH_ANY_EXISTS

On this page

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. 

    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.

    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.

    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:

SELECT BSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 10, '[{"a":10}]':>BSON, '0', 'a') AS Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
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:

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:

SELECT BSON_MATCH_ANY(MATCH_PARAM_BIGINT_STRICT() = 234,@bsonObj,'a','n') AS Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
SELECT BSON_MATCH_ANY(MATCH_PARAM_STRING_STRICT() = "abc",@bsonObj,'a','x') AS Result;
+--------+
| Result |
+--------+
|      1 |
+--------+
SELECT BSON_MATCH_ANY_EXISTS(@bsonObj, 'a', 'y', 'key2') AS Result;
+--------+
| Result |
+--------+
|      1 |
+--------+

Last modified: November 11, 2024

Was this article helpful?

On this page

Was this article helpful?