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?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK