5.3. Updating JSON Data

Documentation

VoltDB Home » Documentation » Guide to Performance and Customization

5.3. Updating JSON Data

The JSON functions can not only query the JSON-encoded data, they let you modify a JSON-encoded string in place. The SET_FIELD() function takes JSON data as input and returns the same JSON structure but with the specified field updated. For example, using the user_session_table described in the previous section, it is possible to update the last_login property in the JSON column session_info with a single SQL statement:

UPDATE user_session_table 
   SET session_info = SET_FIELD(session_info,'properties.last_login',?)
   WHERE username=? AND global_session_id=?;

Again, the JSON functions can be nested to perform more complex operations. In the following example, the UPDATE statement takes a full JSON encoded-string as input for the session_info column, but merges it with the properties value from the existing record.

CREATE PROCEDURE merge_properties AS
   PARTITION ON TABLE user_session_table COLUMN username
   AS
      UPDATE user_session_table 
         SET session_inf = SET_FIELD(?,'properties',
             FIELD(session_info,'properties'))
         WHERE username=?;