# BSON\_BUILD\_OBJECT

Creates a BSON object from the specified \<key, value> pairs.

## Syntax

```
BSON_BUILD_OBJECT([key1,value1 [,...]]])
```

## Arguments

* `key`: A valid `utf8mb4` string that represents a key. It must not contain `\0` characters in the middle of the string.
* `value`: A valid BSON value or an expression that evaluates to a valid BSON value.

## Return Type

A BSON object.

## Remarks

* If the `key` and `value` arguments are not specified, returns an empty BSON object.
* If the specified value is a SQL `NULL`, the corresponding `<key, value>` pair is omitted.
* If only SQL `NULL` values are specified in the value argument, an empty BSON object is returned.
* Returns an error if an odd number of arguments are specified.
* If the input arguments include identical keys, the resulting object has the value corresponding to the last merged value for each respective key.

## Examples

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

```sql
SELECT BSON_BUILD_OBJECT('field1_integer', 54:>BSON, 'field2_boolean', 'true':>BSON, 'field3_string', '"a BSON string"':>BSON, 'field4_array', '[0,1,2]':>BSON):>JSON AS Result;

```

```output

+----------------------------------------------------------------------------------------------------+
| Result                                                                                             |
+----------------------------------------------------------------------------------------------------+
| {"field1_integer":54,"field2_boolean":true,"field3_string":"a BSON string","field4_array":[0,1,2]} |
+----------------------------------------------------------------------------------------------------+
```

```sql
SELECT BSON_BUILD_OBJECT('field1_datetime', '2023-02-06 19:05:37.234':>DATETIME(6):>BSON, 'field2_document', '{"a":123}':>BSON, 'field3_null', 'null':>BSON):>JSON AS Result;

```

```output

+---------------------------------------------------------------------------------------------------------+
| Result                                                                                                  |
+---------------------------------------------------------------------------------------------------------+
| {"field1_datetime":{"$date":"2023-02-06T19:05:37.234Z"},"field2_document":{"a":123},"field3_null":null} |
+---------------------------------------------------------------------------------------------------------+
```

```sql
SELECT BSON_BUILD_OBJECT():>JSON;

```

```output

+--------+
| Result |
+--------+
| {}     |
+--------+
```

In the following example, only the last key/value pair for the key `'field_duplicate'` is added to the BSON object because the input argument has multiple keys named `'field_duplicate'`.

```sql
SELECT BSON_BUILD_OBJECT('field_duplicate', 87:>BSON, 'field2', '"text"':>BSON, 'field_duplicate', '{"a":40}':>BSON):>JSON AS Result;

```

```output

+----------------------------------------------+
| Result                                       |
+----------------------------------------------+
| {"field2":"text","field_duplicate":{"a":40}} |
+----------------------------------------------+
```

In the following example, keys with SQL `NULL` values are ignored and BSON `NULL` values are added to the BSON object.

```sql
SELECT BSON_BUILD_OBJECT("field1", NULL, "field2", NULL, "field3",  null):>JSON AS Result;

```

```output

+--------+
| Result |
+--------+
| {}     |
+--------+
```

```sql
SELECT BSON_BUILD_OBJECT("field1", NULL, "field2", NULL, "field3", 'null':>BSON):>JSON AS Result;

```

```output

+-----------------+
| Result          |
+-----------------+
| {"field3":null} |
+-----------------+
```

***

Modified at: May 8, 2024

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

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