SQLite is a small-scale SQL database project that recently released version 3.45.0, the first version of the year. One significant change in this version is the transformation of the data structure of JSON fields from JSON to JSONB. This modification aims to accelerate the processing of JSON by eliminating the need for direct text parsing in every processing round. However, the functionality remains largely the same, as all SQL functions that previously supported JSON now also support JSONB. Furthermore, most functions that return JSON have equivalent functions that return JSONB as well.
JSONB is a data type introduced by PostgreSQL to enhance the speed of JSON processing. While SQLite does not utilize PostgreSQL’s code, it has implemented its own version of JSONB with some necessary differences. The processing performance may have different big-O complexities for certain operations.
The JSONB field itself is a regular BLOB, and users can directly modify the binary data. However, the project warns that modifying JSONB extensively may lead to unpredictable results due to potential malformed data. It may occasionally return correct values or generate an error indicating a failure to read the data. Additionally, each version of SQLite does not guarantee consistent results.
TLDR: SQLite’s latest version incorporates a significant change by transforming JSON fields into JSONB, resulting in improved processing efficiency. The project warns of potential unpredictability when extensively modifying JSONB, and different versions may yield varying results.
Leave a Comment