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