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?