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