# BSON\_SET\_BSON

Sets (or overwrites) the value at the specified key in a BSON object or array and returns the BSON object or array.

## Syntax

```
BSON_SET_BSON(<input>, <key_path>, <bson_value>)
```

## Arguments

* `<input>`: A valid BSON object or array, or an expression that evaluates to a valid BSON object or array.
* `<key_path>`: The path to the key in the BSON object or array.
* `<bson_value>`: A valid BSON value or en expression that evaluates to a valid BSON value.

## Return Type

A BSON object or array.

## Remarks

* If the specified key does not exist, it is added at the specified path in the BSON object.
* If the path represents an index in the array that doesn't exist, the value is added at the specified index and the missing elements in the array leading to the specified index are added as BSON `NULL`.
* If the `<input>` is not a BSON object or array, it is returned as is.

## Examples

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

The following example updates the value of the key at the specified path.

```sql
SELECT BSON_SET_BSON('{"a":10,"b":true}':>BSON, 'b', '{"$numberDecimal":"125.55"}':>BSON):>JSON AS Result;

```

```output

+------------------------------------------+
| Result                                   |
+------------------------------------------+
| {"a":10,"b":{"$numberDecimal":"125.55"}} |
+------------------------------------------+
```

The following example adds a key at the specified path because the specified path doesn't exist in the BSON object.

```sql
SELECT BSON_SET_BSON('{"a":10,"b":true}':>BSON, 'c', 'd', 'e', '{"$numberDecimal":"125.55"}':>BSON):>JSON AS Result;

```

```output

+---------------------------------------------------------------+
| Result                                                        |
+---------------------------------------------------------------+
| {"a":10,"b":true,"c":{"d":{"e":{"$numberDecimal":"125.55"}}}} |
+---------------------------------------------------------------+
```

The following examples update a value in the array.

```sql
SELECT BSON_SET_BSON('{"a":10,"b":{"x":[0,1,2,3,4]}}':>BSON, 'b', 'x', '0', '"new_val"':>BSON):>JSON AS Result;

```

```output

+----------------------------------------+
| Result                                 |
+----------------------------------------+
| {"a":10,"b":{"x":["new_val",1,2,3,4]}} |
+----------------------------------------+
```

```sql
SELECT BSON_SET_BSON('{"a":10,"b":{"x":[0,1,2,3,4]}}':>BSON, 'b', 'x', '0','a', '1':>BSON):>JSON AS Result;

```

```output

+--------------------------------------+
| Result                               |
+--------------------------------------+
| {"a":10,"b":{"x":[{"a":1},1,2,3,4]}} |
+--------------------------------------+
```

The following example adds a value at the specified index in the array. The missing indexes are added with `NULL` values.

```sql
SELECT BSON_SET_BSON('{"a":10,"b":{"x":[0,1,2,3,4],"f":"string value"}}':>BSON, 'b', 'x', '8', '8':>BSON):>JSON AS Result;

```

```output

+--------------------------------------------------------------------+
| Result                                                             |
+--------------------------------------------------------------------+
| {"a":10,"b":{"f":"string value","x":[0,1,2,3,4,null,null,null,8]}} |
+--------------------------------------------------------------------+
```

***

Modified at: May 8, 2024

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

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