VoltDB provides four functions to help you interact effectively with JSON data stored in VARCHAR columns. Each function takes a JSON-encoded text string as the first argument, followed by other arguments required for the operation.
|ARRAY_ELEMENT(json, array-index)||Returns the value of a specific element in a JSON array.|
|ARRAY_LENGTH(json)||Returns the number of elements in a JSON array|
|FIELD(json, path-name)||Returns the value of a specific field in a JSON structure, where the field is identified by path name.|
|SET_FIELD(json, path-name, new-value)||Returns a JSON structure where the specified field's value is updated.|
In the simple case where the JSON is a flat list of named fields, the FIELD() function can extract the value of one of the fields:
SELECT username, FIELD(session_info,'website') AS url FROM user_session_table WHERE username=?;
The FIELD() can also take a path name for more complex JSON structures. For example, if the properties field is itself a list of named fields, you can drill down into the structure using a path, like so:
SELECT username, FIELD(session_info,'website') AS url, FIELD(session_info,'properties.last_login') AS last_login FROM user_session_table WHERE username=?;
For structures containing arrays, you can use ARRAY_ELEMENT() to extract a specific element from the array by position. For example, the following SELECT statement extracts the first element from a JSON column named recent_history. (The array index is zero-based.)
SELECT username, ARRAY_ELEMENT(recent_history,0) AS most_recent FROM user_history WHERE username=?;
Finally, you can nest the functions to perform more complex queries. Say rather than being a separate column, the named array recent_history is a subelement of the properties field in the session_info column, the following query retrieves the last element from the recent_history array:
SELECT username, ARRAY_ELEMENT(FIELD(session_info,'properties.recent_history'), ARRAY_LENGTH(FIELD(session_info,'properties.recent_history'))-1) AS oldest FROM user_session_table WHERE username=?;
Note that, as mentioned earlier, VoltDB does not validate the correctness of the JSON-encoded string on input into a VARCHAR column. Instead, VoltDB validates the data when one of the JSON functions attempts to parse the encoded string. If the data is not a valid JSON-encoded string, the function will throw an exception at run-time and rollback the transaction.