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
On this page
Creates a BSON object from the specified <key, value> pairs.
Syntax
BSON_BUILD_OBJECT([key1,value1 [,...]]])
Arguments
-
key
: A validutf8mb4
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
andvalue
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.
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]} |
+----------------------------------------------------------------------------------------------------+
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} |
+---------------------------------------------------------------------------------------------------------+
SELECT BSON_BUILD_OBJECT():>JSON;
+--------+
| Result |
+--------+
| {} |
+--------+
In the following example, only the last key/value pair for the key 'field_
is added to the BSON object because the input argument has multiple keys named 'field_
.
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.
SELECT BSON_BUILD_OBJECT("field1", NULL, "field2", NULL, "field3", null):>JSON AS Result;
+--------+
| Result |
+--------+
| {} |
+--------+
SELECT BSON_BUILD_OBJECT("field1", NULL, "field2", NULL, "field3", 'null':>BSON):>JSON AS Result;
+-----------------+
| Result |
+-----------------+
| {"field3":null} |
+-----------------+
Last modified: May 8, 2024