# BSON\_EXTRACT\_BINARY

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_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
  SELECT 
  BSON_EXTRACT_BINARY('{"a":{"$binary":{"base64":"QUJDRA==","subType":"0"}}}':>BSON, 'a') AS Result;

  ```
  ```output

  +--------+
  | Result |
  +--------+
  | ABCD   |
  +--------+
  ```
  ```sql
  SELECT 
  BSON_EXTRACT_BINARY('[{"$binary":{"base64":"MTIzNA==","subType":"02"}}]':>BSON, '0') AS Result;

  ```
  ```output

  +--------+
  | Result |
  +--------+
  | 1234   |
  +--------+
  ```
* Returns SQL `NULL` because the value at the specified path is not a BSON binary value:
  ```sql
  SELECT 
  BSON_EXTRACT_BINARY('{"a":{"$binary":{"base64":"QUJDRA==","subType":"0"}}, "b":50}':>BSON, 'b') AS Result;

  ```
  ```output

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

***

Modified at: July 10, 2025

Source: [/cloud/reference/sql-reference/bson-functions/bson-extract-binary/](https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions/bson-extract-binary/)

(An index of the documentation is available at /llms.txt)