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.
| Function | Description |
|---|---|
| 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:
DocumentationSELECT 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.