The JSON functions perform significant string processing to encode and decode the JSON structures. If the table does not have appropriate indexes, the extra processing required for JSON columns added to the need to scan all of the records in the table can quickly result in undesirable latency. The solution is to index the pertinent JSON operations.
To speed up query execution for JSON queries, you can define an index on the commonly accessed fields, including the commonly used function instances. For example, the following index definitions can significantly improve the execution time for queries described in the previous sections:
CREATE INDEX session_index_last_login ON user_session_table ( field(session_info, 'properties.last_login'), username ); CREATE INDEX session_index_history ON user_session_table ( ARRAY_ELEMENT(FIELD(session_info,'properties.recent_history'), ARRAY_LENGTH(FIELD(session_info,'properties.recent_history'))-1), username );
These are fully functional SQL indexes. Whenever you create or update a record in the user_session_table table, VoltDB executes the JSON functions and stores the result inside the index. When you query by that same function in the future, VoltDB uses the index avoids both the table scan and repeating the JSON string processing.