BSON_EXTRACT_DOUBLE

Syntax

BSON_EXTRACT_DOUBLE(<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 DOUBLE type.

The BSON INT32, INT64, and DOUBLE (except NaN, +infinity, or -infinity) types are returned as is.
BSON DECIMAL128 type is typecast using the Intel RDFP Library. If the converted value is out of range, NULL is returned.
Returns NULL if:
- The extracted value is out of range, a non-numeric BSON type, NaN, +infinity, or -infinity.
- 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_DOUBLE('{"a":{"b":10}}':>BSON, 'a', 'b') AS Result;

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

The following example extracts a BSON INT64 type:

SQL

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

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

The following examples extract a BSON DOUBLE type:

SQL

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

+----------------------+
| Result               |
+----------------------+
| 9.223372036854776e17 |
+----------------------+

SQL

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

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

The following example extracts a BSON DECIMAL type:

SQL

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

+-----------------------+
| Result                |
+-----------------------+
| -9.223372036854776e18 |
+-----------------------+