Syntax

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

Remarks

• BSON_EXTRACT_BINARY() enables support for comparing binary values in BSON_MATCH_ANY() via MATCH_PARAM_BINARY_STRICT().

• If the value at the specified path is not a BSON binary value, BSON_EXTRACT_BINARY() returns SQL NULL.

Examples

Note: The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON.

• Returns the binary value at the specified path:

  SQL

  Copy

  SELECT 
  BSON_EXTRACT_BINARY('{"a":{"$binary":{"base64":"QUJDRA==","subType":"0"}}}':>BSON, 'a') AS Result;

  +--------+
  | Result |
  +--------+
  | ABCD   |
  +--------+

  SQL

  Copy

  SELECT 
  BSON_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:

  SQL

  Copy

  SELECT 
  BSON_EXTRACT_BINARY('{"a":{"$binary":{"base64":"QUJDRA==","subType":"0"}}, "b":50}':>BSON, 'b') AS Result;

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