BSON_ COMPARE
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
Compares two BSON values of similar types.
SingleStore uses the same comparison/sort order for BSON types as MongoDB®.
Syntax
BSON_COMPARE(<input1>, <input2>)
Arguments
<input1>
, <input2>
: Any valid BSON value or an expression that evaluates to a valid BSON value.
Return Type
BSON_
returns the following values:
Comparison |
Output |
---|---|
|
0 |
|
-1 |
|
1 |
Remarks
BSON_
returns NULL
for cross-type comparisons.
Examples
Note:The following examples explicitly cast string to BSON for clarity.
Here are a few examples of valid BSON value comparisons:
SELECT BSON_COMPARE('"string"':>BSON,'"string"':>BSON) AS Result;
+--------+
| Result |
+--------+
| 0 |
+--------+
SELECT BSON_COMPARE('{"$oid":"641044fd4542e7dc53758df9"}':>BSON,'{"$oid":"641044fd4542e7dc53758df9"}':>BSON) AS Result;
+--------+
| Result |
+--------+
| 0 |
+--------+
SELECT BSON_COMPARE('{"$numberLong":"99991241"}':>BSON, 99991241:>BSON) AS Result;
+--------+
| Result |
+--------+
| 0 |
+--------+
SELECT BSON_COMPARE('{"$numberLong":"99991241"}':>BSON, '{"$numberDecimal":"211"}':>BSON) AS Result;
+--------+
| Result |
+--------+
| 1 |
+--------+
SELECT BSON_COMPARE('null':>BSON,'null':>BSON) AS Result;
+--------+
| Result |
+--------+
| 0 |
+--------+
The following examples return NULL
because of cross-type value comparisons:
SELECT BSON_COMPARE('{"$numberLong":"99991241"}':>BSON, '"99991241"':>BSON) AS Result;
+--------+
| Result |
+--------+
| NULL |
+--------+
SELECT BSON_COMPARE('{"$date":"2023-03-14T09:48:10.720Z"}':>BSON, '"2023-03-14T09:48:10.720Z"':>BSON) AS Result;
+--------+
| Result |
+--------+
| NULL |
+--------+
Last modified: May 8, 2024