BSON_ NORMALIZE_ *
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
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_
: Normalizes the BSON document for equality comparisons (such asNORMALIZE() $eq
). -
BSON_
: Normalizes the BSON document for ascending sort or less than comparisons, (such asNORMALIZE_ ASC() $lt
).For arrays, the smallest element is selected. -
BSON_
: Normalizes the BSON document for descending sort or greater than comparisons, (such asNORMALIZE_ DESC() $gt
).For arrays, the largest element is selected. -
BSON_
: Similar toNORMALIZE_ NO_ ARRAY() BSON_
, it normalizes the BSON document for equality comparisons (such asNORMALIZE() $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_
: 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