BSON_UPDATE function

The BSON_UPDATE function is a built-in SQL function that updates the contents of a BSON column with MongoDB update operators.

Syntax

BSON_UPDATE function

BSON_UPDATE ( bson_column , " update_doc " )
Element Description Restrictions Syntax
bson_column Name of a BSON column Must exist Identifier
update_doc A JSON or BSON document that contains one or more update operations.

The following MongoDB API update operators are allowed:

  • $set
  • $inc
  • $unset

The following MongoDB API array update operators are allowed:

  • $addToSet
  • $pop
  • $pullAll
  • $push

The following array update operator modifiers are allowed:

  • $each
  • $position
  • $slice
  • $sort
 You can use each update operator only once in the update_doc.

JavaScript expressions are not allowed.

 Quoted String

Usage

Use the BSON_UPDATE function to update a BSON document with the supported MongoDB API update operators. Update operations are run sequentially: the original document is updated and then the updated document is the input to the next update operator. The BSON_UPDATE function returns the final BSON document.

To include JavaScript expressions in your update operations, evaluate the expressions on the client side and supply the final result of the expression in a field-value pair.

If you attempt to use a MongoDB API update operator that is not supported, you receive the following error message:

9659: The server does not support the specified UPDATE operation on JSON documents.

Example: Update one value

The following example updates the value of the id field with the $set operator, where the value of the name field matches Joe:

CREATE DATABASE testdb WITH LOG;
CREATE TABLE bson_table(bson_col BSON);

INSERT INTO bson_table VALUES('{id:150,name:"Joe"}'::JSON::BSON);
INSERT INTO bson_table VALUES('{id:101,name:"Mike"}'::JSON::BSON);
INSERT INTO bson_table VALUES('{id:102,name:"Nick"}'::JSON::BSON);

UPDATE bson_table 
                    SET bson_col = bson_update(bson_col, '{$set:{id:150}}') 
                    WHERE BSON_GET(bson_col,"name") = '{"name":"Joe"}'::JSON::BSON;

1 row(s) updated.

> SELECT bson_col::JSON FROM bson_table;

(expression)  {"id":150,"name":"Joe"} 

(expression)  {"id":101,"name":"Mike"} 

(expression)  {"id":102,"name":"Nick"} 

3 row(s) retrieved.

Example: Update multiple values

The following statement shows what the result of using the $set operator to update the name Nick to Nick2, and using the $inc operator to increase Nick's ID number by 200:

SELECT bson_update(bson_col, '{"$set":{"name":"Nick2"},$inc:{id:200}}')::JSON 
       FROM bson_table 
       WHERE BSON_GET(bson_col,"name") = '{"name":"Nick"}'::JSON::BSON;

(expression)  {"id":302,"name":"Nick2"}

Example: Update an array to add an item

The following statement adds an item to an array with the $push operator:

SELECT bson_update(c, '{$push:{ar:"four"}}')::json 
    FROM t;

(expression)
     {"_id":1,"position":{"x":10,"y":30},"ar":["one",2,"three","four"]} 

1 row(s) retrieved.

Example: Update an array with multiple operators

The following statement sets the value of two positions in the array with the $set operator, adds three values to the array with the $push operator and the $each modifier, and then sorts values in the array in descending order with the $sort modifier:

SELECT bson_update(c, '{$set:{"ar.0":1, "ar.2":3}, 
              $push:{ar:{$each:[6, 4, 5], $sort:-1}}}')::json 
    FROM t; 

(expression)
     {"_id":1,"position":{"x":10,"y":30},"ar":[6,5,4,3,2,1]} 

1 row(s) retrieved.