BSON_EXTRACT_BIGINT

Syntax

BSON_EXTRACT_BIGINT(<input> [, path_key_1, path_key_2,.., path_key_n])

<input>: A valid BSON document or an expression that evaluates to a valid BSON document.
<path>: A comma-separated list of dictionary keys or array positions (zero-based index).

SingleStore BIGINT type.

The BSON INT32 and INT64 types are returned as is.
BSON DOUBLE and DECIMAL128 types are typecast using the Intel RDFP Library. If the converted value is out-of-range, NULL is returned.
BSON_EXTRACT_BIGINT() returns NULL if:
- The extracted value is out-of-range or a non-numeric BSON type
- The specified path does not exist

Note: The following examples explicitly cast string to BSON for clarity.

The following example extracts a BSON INT32 type:

SQL

SELECT BSON_EXTRACT_BIGINT('{"a":{"b":10}}':>BSON, 'a', 'b') AS Result;

+--------+
| Result |
+--------+
|     10 |
+--------+

The following example extracts a BSON INT64 type:

SQL

SELECT BSON_EXTRACT_BIGINT('{"a":{"b":{"$numberLong":"10"}}}':>BSON, 'a', 'b') AS Result;

+--------+
| Result |
+--------+
|     10 |
+--------+

The following examples extract a BSON DOUBLE type:

SQL

SELECT BSON_EXTRACT_BIGINT('{"a":{"b":{"$numberDouble":"922337203685477586.9"}}}':>BSON, 'a', 'b') AS Result;

+--------------------+
| Result             |
+--------------------+
| 922337203685477632 |
+--------------------+

SQL

SELECT BSON_EXTRACT_BIGINT('{"a":{"b":{"$numberDouble":"NaN"}}}':>BSON, 'a', 'b') AS Result;

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

The following example extracts a BSON DECIMAL type:

SQL

SELECT BSON_EXTRACT_BIGINT('{"a":{"b":{"$numberDecimal":"-9223372036854775807.9"}}}':>BSON, 'a', 'b')AS Result;

+----------------------+
| Result               |
+----------------------+
| -9223372036854775808 |
+----------------------+