# BSON\_EXTRACT\_STRING

Extracts the string value for the specified key from the BSON data, converts it to a SingleStore string type, and returns the value.

## Syntax

```
BSON_EXTRACT_STRING(<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 string.

## Remarks

* The BSON string types are returned as is.
* The default collation for this function is `utf8mb4_bin`.
* Returns `NULL` if:

  * The extracted value is an invalid BSON string, a BSON type other than string, or `NULL`.
  * The specified path does not exist.

## Examples

**Note**: The following examples explicitly cast string to BSON for clarity.

The following example extracts a string:

```sql
SELECT BSON_EXTRACT_STRING('{"a":{"b":"A valid string"}}':>BSON, 'a', 'b') AS Result; 

```

```output

+----------------+
| Result         |
+----------------+
| A valid string |
+----------------+
```

The following examples return `NULL` because of invalid input types:

```sql
SELECT BSON_EXTRACT_STRING('{"a":{"b":{"$numberDecimal":"12.5"}}}':>BSON, 'a', 'b') AS Result; 

```

```output

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

```sql
SELECT BSON_EXTRACT_STRING('{"a":{"b":{"$date":"2023-03-14T09:48:10.720Z"}}}':>BSON, 'a', 'b') AS Result; 

```

```output

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

```sql
SELECT BSON_EXTRACT_STRING('{"a":{"b":{"$oid":"641044fd4542e7dc53758df9"}}}':>BSON, 'a', 'b') AS Result; 

```

```output

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

***

Modified at: April 2, 2024

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

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