Updating JSON data in a table

You can use the JSON2BSON function to update JSON data in a table.

Procedure

To update JSON data in a table:

  1. Query and retrieve the JSON data from a JSON column.
  2. Modify the contents of the JSON column.
  3. To push the updated column value back into the table, issue the UPDATE SQL statement and specify the JSON2BSON function.

Example

The following example updates the JSON document in the table:
UPDATE CLAIMS SET CALL_RECORDS = SYSTOOLS.JSON2BSON('{"calls": [ 
                                                     {"dateOfCall": "2014-02-26",
                                                      "associate": "Steven Barnes",    
                                                      "conversationNotes": "Customer provided details of accident 
                                                      and witness contact information.  Stated that her local 
                                                      agent was unavailable."},
                                                     {"dateOfCall": "2014-02-29",
                                                      "associate": "Paula Garcia",
                                                      "conversationNotes": "Client asked about status of the 
                                                      damage estimate."}],
                                                      "satisfactionSurveyResult":"needs improvement" }');