Sign inTry Free

BSON_MATCH_ANY and BSON_MATCH_ANY_EXISTS

On this page

BSON_MATCH_ANY returns 1 if any BSON value at the specified filter path matches the filter predicate in a BSON object. BSON_MATCH_ANY_EXISTS returns 1 if the specified filter path exists in the BSON object.

Syntax

BSON_MATCH_ANY(<filter_predicate>, <bson>, <filter_path>)

BSON_MATCH_ANY_EXISTS(<bson>,<filter_path>)

Arguments

  • <bson>: A valid BSON object or an expression that evaluates to a valid BSON object.

  • <filter_path>: A comma separated list of keys or array positions (zero-based index) that represents the path.

  • <filter_predicate>: A SQL expression that is applied to each matching value at the filter path. Supports MATCH_PARAM_<type>() function to indicate where the matched values are substituted in the filter predicate. Refer to Remarks for more information.

Return Type

A TINYINT value.

Remarks

  • BSON_MATCH_ANY() returns 1 if any value at the specified filter path evaluates the filter predicate to true.

  • The filter path is matched as:

    • If the current element is a BSON document, the next key in the filter path is matched as a key in the document.

    • If the current element is an array, the next key in the filter path is either matched as a zero-based index in the array or a key of any document that is an element in the array.

    • If the next key in the filter path can be matched both as a BSON document and an array, BSON_MATCH_ANY() matches the zero-based index in the array.

    For example, both of the following queries 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 |
+--------+

MATCH_PARAM_<type>()

  • MATCH_PARAM_<type>() functions can only be used within the <filter_predicate> argument.

  • The behavior of each MATCH_PARAM_<type>() function is identical to the corresponding BSON_EXTRACT_<type>() function. Refer to BSON Functions for more information.

  • The following MATCH_PARAM_<type>() functions are supported:

    MATCH_PARAM_BIGINT_STRICT()

    MATCH_PARAM_BOOL_STRICT()

    MATCH_PARAM_BSON()

    MATCH_PARAM_DATETIME_STRICT()

    MATCH_PARAM_DOUBLE_STRICT()

    MATCH_PARAM_STRING_STRICT()

Examples

The examples in this section use the following 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: June 11, 2024

Was this article helpful?

On this page

Was this article helpful?