BSON_ UPDATE
On this page
Updates a BSON document.
Accepts two BSON documents, updates the first document using the specified update operators in the second document (similar to the MongoDB® update operators), and returns the updated document.
Syntax
BSON_UPDATE(<bson_to_update>, <update>)
Arguments
-
bson_
: A valid BSON document or an expression that evaluates to a valid BSON document to update.to_ update -
update
: A valid BSON document that specifies the update operation to perform.
Return Type
A BSON
value.
Remarks
BSON_
does not support the following update operators:
-
$pull
-
$rename
-
$setOnInsert
Examples
Note: The following examples explicitly cast string to BSON for clarity.
The following are examples of different update operations on a BSON document or array:
-
Multiply
SELECT BSON_UPDATE('{"a":100, "b":50}':>BSON, '{"a":[{"$mul":2}]}':>BSON):>JSON AS Result;+------------------+ | Result | +------------------+ | {"a":200,"b":50} | +------------------+
-
Increment
SELECT BSON_UPDATE('{"a":100}':>BSON, '{"a":[{"$inc":50}]}':>BSON):>JSON AS Result;+-----------+ | Result | +-----------+ | {"a":150} | +-----------+
-
Add a new field
SELECT BSON_UPDATE('{"a":100, "b":50}':>BSON, '{"c":[{"$set":"New field"}]}':>BSON):>JSON AS Result;+----------------------------------+ | Result | +----------------------------------+ | {"a":100,"b":50,"c":"New field"} | +----------------------------------+
-
Remove a field
SELECT BSON_UPDATE('{"a":100, "b":50}':>BSON, '{"b":[{"$unset":1}]}':>BSON):>JSON AS Result;+-----------+ | Result | +-----------+ | {"a":100} | +-----------+
-
Add elements to an array
SELECT BSON_UPDATE('{"a":[1,2,3]}':>BSON, '{"a":[{"$addToSet":{"$each":[4,5,4]}}]}':>BSON):> JSON AS Result;+-------------------+ | Result | +-------------------+ | {"a":[1,2,3,4,5]} | +-------------------+
SELECT BSON_UPDATE('{"a":[1,2,3]}':>BSON, '{"a":[{"$push":{"$each":[4,5]}}]}':>BSON):>JSON AS Result;+-------------------+ | Result | +-------------------+ | {"a":[1,2,3,4,5]} | +-------------------+
-
Remove elements from an array
SELECT BSON_UPDATE('{"a":[1,2,3,4,5]}':>BSON, '{"a":[{"$pop":1}]}':>BSON):> JSON AS Result;+-----------------+ | Result | +-----------------+ | {"a":[1,2,3,4]} | +-----------------+
SELECT BSON_UPDATE('{"a":[1,1,2,2,3,4,4,5]}':>BSON, '{"a":[{"$pullAll":[1,4]}]}':>BSON):> JSON AS Result;+-----------------+ | Result | +-----------------+ | {"a":[2,2,3,5]} | +-----------------+
Last modified: July 10, 2025