Syntax

BSON_UPDATE(<bson_to_update>, <update>)

Remarks

BSON_UPDATE() does not support the following update operators:

• $pull

• $rename

• $setOnInsert

Examples

Note: The following examples explicitly cast string to BSON for clarity. Similarly, the output is cast to JSON.

The following are examples of different update operations on a BSON document or array:

• Multiply

  SQL

  Copy

  SELECT BSON_UPDATE('{"a":100, "b":50}':>BSON, '{"a":[{"$mul":2}]}':>BSON):>JSON AS Result;

  +------------------+
  | Result           |
  +------------------+
  | {"a":200,"b":50} |
  +------------------+

• Increment

  SQL

  Copy

  SELECT BSON_UPDATE('{"a":100}':>BSON, '{"a":[{"$inc":50}]}':>BSON):>JSON AS Result;

  +-----------+
  | Result    |
  +-----------+
  | {"a":150} |
  +-----------+

• Add a new field

  SQL

  Copy

  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

  SQL

  Copy

  SELECT BSON_UPDATE('{"a":100, "b":50}':>BSON, '{"b":[{"$unset":1}]}':>BSON):>JSON AS Result;

  +-----------+
  | Result    |
  +-----------+
  | {"a":100} |
  +-----------+

• Add elements to an array

  SQL

  Copy

  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]} |
  +-------------------+

  SQL

  Copy

  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

  SQL

  Copy

  SELECT BSON_UPDATE('{"a":[1,2,3,4,5]}':>BSON, '{"a":[{"$pop":1}]}':>BSON):> JSON AS Result;

  +-----------------+
  | Result          |
  +-----------------+
  | {"a":[1,2,3,4]} |
  +-----------------+

  SQL

  Copy

  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]} |
  +-----------------+