# BSON\_COMPARE

Compares two BSON values of similar types.

SingleStore uses the same [comparison/sort order](https://www.mongodb.com/docs/manual/reference/bson-type-comparison-order/#std-label-bson-types-comparison-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_COMPARE()` returns the following values:

| Comparison                          | Output |
| ----------------------------------- | ------ |
| `<input1>`equals`<input2>`          | 0      |
| `<input1>`is less than`<input2>`    | -1     |
| `<input1>`is greater than`<input2>` | 1      |

## Remarks

`BSON_COMPARE()` returns `NULL` for cross-type comparisons.

## Examples

**Note**:The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON.

Here are a few examples of valid BSON value comparisons:

```sql
SELECT BSON_COMPARE('"string"':>BSON,'"string"':>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|      0 |
+--------+
```

```sql
SELECT BSON_COMPARE('{"$oid":"641044fd4542e7dc53758df9"}':>BSON,'{"$oid":"641044fd4542e7dc53758df9"}':>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|      0 |
+--------+
```

```sql
SELECT BSON_COMPARE('{"$numberLong":"99991241"}':>BSON, 99991241:>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|      0 |
+--------+

```

```sql
SELECT BSON_COMPARE('{"$numberLong":"99991241"}':>BSON, '{"$numberDecimal":"211"}':>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|      1 |
+--------+
```

```sql
SELECT BSON_COMPARE('null':>BSON,'null':>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|      0 |
+--------+
```

The following examples return `NULL` because of cross-type value comparisons:

```sql
SELECT BSON_COMPARE('{"$numberLong":"99991241"}':>BSON, '"99991241"':>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|   NULL |
+--------+
```

```sql
SELECT BSON_COMPARE('{"$date":"2023-03-14T09:48:10.720Z"}':>BSON, '"2023-03-14T09:48:10.720Z"':>BSON) AS Result;

```

```output

+--------+
| Result |
+--------+
|   NULL |
+--------+
```

***

Modified at: May 8, 2024

Source: [/db/v9.1/reference/sql-reference/bson-functions/bson-compare/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/bson-functions/bson-compare/)

(An index of the documentation is available at /llms.txt)