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_ 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