# BSON\_EXTRACT\_BSON

Extracts and returns a BSON value from the BSON object, optionally at the specified path.

## Syntax

```
BSON_EXTRACT_BSON(<bson_input> [, path_key_1, path_key_2, ..., path_key_n])
```

## Arguments

* `<bson_input>`: A valid BSON object or an expression that evaluates to a valid BSON object.
* `<path_key>`: (Optional) A comma-separated list of dictionary keys or array positions (zero-based index) that represent the path to the BSON object or the value to extract.

## Return Type

A BSON value.

## Remarks

* If the `<path_key>` argument is not specified, the top-level document or value is returned.
* If the input is a top-level BSON value, `BSON_EXTRACT_BSON()` returns the input value if the `<path_key>` argument is not specified. Otherwise, it returns `NULL`.
* 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.

The examples use the following data set:

```sql
CREATE DATABASE dbTest;
USE dbTest;

CREATE TABLE bsonExample(a BSON);
INSERT INTO bsonExample VALUES('{"doc":{"field1":42,"field2":true,"field3":"foobar","field4":[1,2,3],"child_doc":{"a":123}}}':>BSON);
INSERT INTO bsonExample VALUES('{"doc":{"field1":28,"field2":false,"field3":"barfoo","field4":[7,8,9],"child_doc":{"a":42}}}':>BSON);
INSERT INTO bsonExample VALUES('{"doc":{"field1":32,"field2":true,"field3":"hohoho","field4":[3,2,1],"child_doc":{"a":488}}}':>BSON);
```

The following example returns the top level document because the `<path_key>` is not specified:

```sql
SELECT BSON_EXTRACT_BSON(a):>JSON AS Result FROM bsonExample;

```

```output

+----------------------------------------------------------------------------------------------+
| Result                                                                                       |
+----------------------------------------------------------------------------------------------+
| {"doc":{"child_doc":{"a":123},"field1":42,"field2":true,"field3":"foobar","field4":[1,2,3]}} |
| {"doc":{"child_doc":{"a":42},"field1":28,"field2":false,"field3":"barfoo","field4":[7,8,9]}} |
| {"doc":{"child_doc":{"a":488},"field1":32,"field2":true,"field3":"hohoho","field4":[3,2,1]}} |
+----------------------------------------------------------------------------------------------+
```

The following example returns the BSON document at the specified path:

```sql
SELECT BSON_EXTRACT_BSON(a, 'doc','child_doc'):>JSON AS Result FROM bsonExample;

```

```output

+-----------+
| Result    |
+-----------+
| {"a":42}  |
| {"a":488} |
| {"a":123} |
+-----------+
```

The following example returns the BSON array at the specified path:

```sql
SELECT BSON_EXTRACT_BSON(a, 'doc', 'field4'):>JSON AS Result FROM bsonExample;

```

```output

+---------+
| Result  |
+---------+
| [3,2,1] |
| [7,8,9] |
| [1,2,3] |
+---------+
```

The following example returns `NULL` because the specified `<path_key>` does not exist.

```sql
SELECT BSON_EXTRACT_BSON(a, 'doc', 'doc'):>JSON AS Result FROM bsonExample;

```

```output

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

***

Modified at: May 8, 2024

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

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