# BSON\_EXTRACT\_ALL\_BSON

Extracts and returns all the values from a BSON document or array, optionally at the specified path.

If the specified path matches an array, `BSON_EXTRACT_ALL_BSON()` recursively extracts and aggregates values from each element at the specified path.

## Syntax

```
BSON_EXTRACT_ALL_BSON(<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 `BSON` value.

## Remarks

* If the `path_key` argument is not specified, the top-level document or value is returned.
* If the specified `path_key` does not exist, returns `NULL`.

## Examples

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

* Returns values for all the matching keys in the BSON document:
  ```sql
  SELECT 
  BSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON, 'c', 'a', 'b'):>JSON AS Result;

  ```
  ```output

  +-------------+
  | Result      |
  +-------------+
  | [[1,2,3,4]] |
  +-------------+
  ```
  ```sql
  SELECT 
  BSON_EXTRACT_ALL_BSON('{"a":[{"b":[1,2]},{"b":[3,4]},{"b":[5,6]},{"b":[7,8]}]}':>BSON, 'a', 'b'):>JSON AS Result;

  ```
  ```output

  +---------------------------+
  | Result                    |
  +---------------------------+
  | [[1,2],[3,4],[5,6],[7,8]] |
  +---------------------------+
  ```
* Returns the top-level document because the path is not specified:
  ```sql
  SELECT 
  BSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON):>JSON AS Result;

  ```
  ```output

  +-------------------------------------------------+
  | Result                                          |
  +-------------------------------------------------+
  | {"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]} |
  +-------------------------------------------------+
  ```
* Returns `NULL` because the specified path does not exist in the BSON document:
  ```sql
  SELECT 
  BSON_EXTRACT_ALL_BSON('{"c":[{"a":[{"b":1},{"b":2},{"b":3},{"b":4}]}]}':>BSON, 'c', 'd'):>JSON AS Result;

  ```
  ```output

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

***

Modified at: July 10, 2025

Source: [/db/v9.1/reference/sql-reference/bson-functions/bson-extract-all-bson/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/bson-functions/bson-extract-all-bson/)

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