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.

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.