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.
BSON_ EXTRACT_ BINARY
On this page
Extracts and returns the BSON binary value while ignoring the subtype, optionally at the specified path.
Syntax
BSON_EXTRACT_BINARY(<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
A LONGBLOB
value.
Remarks
-
BSON_
enables support for comparing binary values inEXTRACT_ BINARY() BSON_
viaMATCH_ ANY() MATCH_
.PARAM_ BINARY_ STRICT() -
If the value at the specified path is not a BSON binary value,
BSON_
returns SQLEXTRACT_ BINARY() NULL
.
Examples
Note: The following examples explicitly cast string to BSON for clarity.
-
Returns the binary value at the specified path:
SELECTBSON_EXTRACT_BINARY('{"a":{"$binary":{"base64":"QUJDRA==","subType":"0"}}}':>BSON, 'a') AS Result;+--------+ | Result | +--------+ | ABCD | +--------+
SELECTBSON_EXTRACT_BINARY('[{"$binary":{"base64":"MTIzNA==","subType":"02"}}]':>BSON, '0') AS Result;+--------+ | Result | +--------+ | 1234 | +--------+
-
Returns SQL
NULL
because the value at the specified path is not a BSON binary value:SELECTBSON_EXTRACT_BINARY('{"a":{"$binary":{"base64":"QUJDRA==","subType":"0"}}, "b":50}':>BSON, 'b') AS Result;+--------+ | Result | +--------+ | NULL | +--------+
Last modified: July 10, 2025