BSON_NORMALIZE_*

Normalizing BSON documents allows you to perform the following operations with improved performance:

  • Cross-type comparison for numeric types.

  • Compare across multiple data types, as defined in Comparison/Sort Order.

  • Build indexes using normalized computed columns.

  • Sort and hash BSON documents.

This is a set of four BSON functions that normalize BSON documents.

  • BSON_NORMALIZE(): Normalizes the BSON document for equality comparisons (such as $eq).

  • BSON_NORMALIZE_ASC(): Normalizes the BSON document for ascending sort or less than comparisons, (such as $lt). For arrays, the smallest element is selected.

  • BSON_NORMALIZE_DESC(): Normalizes the BSON document for descending sort or greater than comparisons, (such as $gt). For arrays, the largest element is selected.

  • BSON_NORMALIZE_NO_ARRAY(): Similar to BSON_NORMALIZE(), it normalizes the BSON document for equality comparisons (such as $eq), but returns an error if the BSON document contains an array at any level.

Syntax

BSON_NORMALIZE(<bson_input>)

BSON_NORMALIZE_ASC(<bson_input>)

BSON_NORMALIZE_DESC(<bson_input>)

BSON_NORMALIZE_NO_ARRAY(<bson_input>)

Argument

<bson_input>: A valid BSON document or an expression that evaluates to a valid BSON document.

Examples

Note: The following example explicitly casts a string to BSON for clarity.

SELECT BSON_NORMALIZE('0':>BSON) = BSON_NORMALIZE('{"$numberDouble":"0"}':>BSON) AS Result;
+--------+
| Result |
+--------+
|      1 |
+--------+

Last modified: May 8, 2024

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