Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

BSON_EXTRACT_DOUBLE

Syntax

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

Arguments

<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).

Return Type

SingleStore DOUBLE type.

Remarks

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.

Examples

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 |
+-----------------------+