JSONB is a rewrite of the SQLite JSON functions that, depending on usage patterns, could be several times faster than the original JSON functions. This enhancement has now landed on trunk.
Developers who use JSON heavily in their applications are encouraged to download a pre-release snapshot and give the new code a try.
How Is This Different?
Functions that deal with text JSON use a three-step process:
Parse the text JSON into an internal binary format that is more accessible to C-code.
Carry out the requested operation. Maybe this is looking up a field in an object, or perhaps it is modifying the JSON in some way.
If the processing involved changing the JSON, convert the internal binary format back into RFC-8279 JSON text for output and/or storage.
Step 2 is the essential operation that you want to accomplish. Steps 1 and 3 are just overhead.
Historically, SQLite used an internal binary representation of JSON that involved lots of pointers. This fits will into C programs, but it is difficult to serialize. The JSONB rewrite changes the internal-use binary representation of JSON into a contiguous byte array that can read or written as an SQL BLOB. This allows the internal-use representation of JSON to potentially be saved to the database, in place of JSON text, eliminating the overhead of steps 1 and 3.
What has changed?
All legacy functionality is preserved. The only change has been to add new capabilities.
Any JSON function that accepts JSON text as an input will now also accept JSONB binary content for that same parameter. You do not have to tell the function if it getting text or binary data. It figures that out for itself.
JSON functions that output JSON now come in two versions. The historical "json_" functions works as before. But there is now a corresponding "jsonb_" function that returns JSONB rather than text JSON, thus omitting step 3 in the normal processing.
If you don't make any changes to your application, everything should continue to work as it always has, though perhaps slightly (1%) faster.
But if you modify your application to start storing JSONB instead of text JSON, you might see a 3-times performance improvement, at least for the JSON-intensive operations. JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON.
Migrating
Note that all functions accept both text JSON and JSONB. So to start using JSONB, you do not have to modify your database files to convert legacy text JSON into JSONB. Just start writing out JSONB for new entries. The old entries will continue to work. The new entries will just work faster.
Or, if you do want to convert all your legacy data to JSONB, you can just run an update operation like:
UPDATE bigtable SET jsonColumn = jsonb(jsonColumn);
Please provide comments
If you find this enhancement useful, or if you try it out and see performance regressions or bugs, please let us know. Leave a follow-up post here, or contact me directly at drh at sqlite dot org.
The current plan is to release the JSONB enhancement in the next major release of SQLite - version 3.45.0. That will probably occur in a month or two.