Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

BSON_BUILD_OBJECT

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;

+----------------------------------------------------------------------------------------------------+
| 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;

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

SQL

SELECT BSON_BUILD_OBJECT():>JSON;

+--------+
| 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;

+----------------------------------------------+
| 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;

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

SQL

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

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