BSON_SET_BSON

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;

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

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

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

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

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