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=?;