Version 3.45 of SQLite adds support for JSONB, a binary representation of JSON for internal use, but the format is not the same as JSONB in PostgreSQL.

SQLite added JSON (JavaScript Object Notation) functions in its default build to version 3.38 in February 2022. JSON was stored as text. As the documentation explains: “Backwards compatibility constraints mean that SQLite is only able to store values that are NULL, integers, floating-point numbers, text, and BLOBs. It is not possible to add a new JSON type.” 

Now SQLite can also store JSON as a BLOB type, a binary representation of JSON. The purpose, aside from saving a small amount of disk space, is to avoid “the overhead of parsing and rendering JSON when reading and updating JSON values.” The JSON functions now have corresponding JSONB functions, which return this binary format, and all JSON functions now accept JSON in either text or JSONB format.

PostgreSQL, another hugely popular open source relational database, added JSONB support in version 9.4 in 2014 and with the same motive, as explained by co-author Andrew Dunstan in a post at the time. “The new format accepts exactly the same data as the json type. However, it is stored in a format that does not require reparsing the original text in order to process it, making it much more suitable for indexing and other operations,” wrote Dunstan.

SQLite’s JSONB is “inspired by PostgreSQL, but the on-disk format for SQLite’s JSONB is not the same as PostgreSQL’s,” states the documentation for the new release.

Should developers care about the JSONB format? The SQLite devs, including primary author D Richard Hipp, evolved their views on this while the feature was in preparation. An early version of the documentation includes a description of “the core idea behind SQLite’s JSONB” explaining why it is faster for processing and adding that “JSONB is stored in database files which are intended to be readable and writable for many decades into the future. To that end, the JSONB format is well-defined and stable.” A link to full details of the format was planned.

Last December though, Hipp revised the draft, noting the decision to “Undocument the JSONB format. Application developers should not know what the internal design of JSONB looks like. JSONB should be just a proprietary internal data structure for SQLite.”

SQLite is open source so the format is not hidden, but it is important that developers understand that it is intended as an internal format and not one that other applications should consume or emit. 

The point of confusion, perhaps, is that using the same name as a long-standing feature of PostgreSQL and having functions for JSONB makes it look like a standard format, even though it is not.

Nevertheless, the promise that “if you modify your application to start storing JSONB instead of text JSON, you might see a three-times performance improvement,” according to a post by Hipp last month, along with disk space savings, makes this a welcome feature for what may be the world’s most widely deployed database engine.