JSON1 Query Engine

Version Compatibliity

The JSON1 Query Engine was introduced in JourneyApps Container v4.37.

The JSON1 Query Engine is a rewrite of the query engine implementation in the container. It affects how indexes are stored, and how queries are performed.

The new query engine has many advantages:

• Up to 8x faster queries than before when no index is used, and 1.5x when an index is already used. Note that indexes will still speed up the query significantly, but slightly less than before.

• Less storage overhead when adding indexes, making initial sync faster.

• Uses the indexes for more types of queries.

Note that performance will not improve noticeably when:

• A query returns all or most objects of a model (no filtering).

• The overhead is in rendering instead of in the database query (e.g. when displaying a thousands of objects in an object-table).

• Queries have already been optimized with good indexes.

Feature Flag: Validation Mode for JSON1 Query Engine

In this mode, queries will be slower than before. In this mode, both the previous and the new query engine is used for each query, and the results compared. If there is a mismatch, an error is raised.

This is useful for testing the feature on an app, but don't enable this for any production deployments.

Technical Details

In the container, we use SQLite as the underlying database engine. However, to enable our sync system as well as data model changes without complex migration scripts, we store all fields as JSON in the table.

Previously we implemented queries by loading all the data in memory and then filtering in JavaScript. When we added indexing support, this was done with a custom implementation, which added a lot of overhead, and lacked support for many types of queries.

The JSON1 query engine still stores the data in the same format. However, we make use of the JSON1 extension in SQLite, which allows us to use native SQLite functionality to query for the data, as well as to use native SQLite features. Because this moves the query functionality from JavaScript to SQLite, we can get the performance gains mentioned above.