Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

BSON_NORMALIZE_*

The BSON_NORMALIZE_*() function is a set of four BSON functions that normalize BSON documents and 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.

The BSON_NORMALIZE_*() function includes the following BSON functions:

  • 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:

Was this article helpful?