Anton Zhiyanov
projects books blog about

SQLite 3.45: Interactive release notes

Based on the SQLite 3.45 release notes from the SQLite team, with interactive examples added.

SQLite 3.45 is out, and it has some decent features, so I think now it's a good time to show them off. Let's get started!

JSONB format

Starting with this release, SQLite allows its internal "parse tree" representation of JSON to be stored on disk, as a BLOB, in a format that called "JSONB". By storing SQLite's internal binary representation of JSON directly in the database, applications can bypass the overhead of parsing and rendering JSON when reading and updating JSON values. The internal JSONB format is also uses slightly less disk space then text JSON.

Any SQL function parameter that accepts text JSON as an input also accepts a BLOB in the JSONB format. The function operates the same in either case, except that it runs faster when the input is JSONB, since it does not need to run the JSON parser:

select json_extract(json('{"name":"alice"}'), '$.name');
select json_extract(jsonb('{"name":"alice"}'), '$.name');

select json('{"name":"alice"}') -> '$.name';
select jsonb('{"name":"alice"}') -> '$.name';

select json('[11,12,13]') -> 1;
select jsonb('[11,12,13]') -> 1;

alice
alice
"alice"
"alice"
12
12

JSONB is a binary representation of JSON used by SQLite and is intended for internal use by SQLite only. Applications should not use JSONB outside of SQLite nor try to reverse-engineer the JSONB format.

The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but are not binary compatible.

  • The PostgreSQL JSONB format claims to offer O(1) lookup of elements in objects and arrays. SQLite's JSONB format makes no such claim.
  • SQLite's JSONB has O(N) time complexity for most operations in SQLite, just like text JSON.

The advantage of JSONB in SQLite is that it is smaller and faster than text JSON — potentially several times faster. There is space in the on-disk JSONB format to add enhancements and future versions of SQLite might include options to provide O(1) lookup of elements in JSONB, but no such capability is currently available.

JSONB functions

Most SQL functions that return JSON text have a corresponding function that returns the equivalent JSONB. The functions that return JSON in the text format begin with json_ and functions that return the binary JSONB format begin with jsonb_.

The jsonb, jsonb_array and jsonb_object return the binary JSONB representation:

select typeof(jsonb('{"name":"alice"}'));
select jsonb('{"name":"alice"}') -> '$';

select typeof(jsonb_array(11,12,13));
select jsonb(jsonb_array(11,12,13)) -> '$';

select typeof(jsonb_object('name', 'alice'));
select jsonb(jsonb_object('name', 'alice')) -> '$';

blob
{"name":"alice"}
blob
[11,12,13]
blob
{"name":"alice"}

When extracting text, numeric, null, or boolean values jsonb_extract works exactly the same as json_extract. When extracting an array or object — returns the value in JSONB format:

select jsonb_extract(jsonb('{"name":"alice"}'), '$.name');
select jsonb_extract(jsonb('[11,12,13]'), '$[1]');

select typeof(
  jsonb_extract(jsonb('{"vals":[11,12,13]}'), '$.vals')
);

alice
12
blob

The -> operator returns JSON always returns the RFC 8565 text representation of that JSON, not JSONB:

select json('{"vals":[11,12,13]}') -> '$.vals';
select jsonb('{"vals":[11,12,13]}') -> '$.vals';

[11,12,13]
[11,12,13]

The jsonb_insert, jsonb_replace, and jsonb_set functions work just like their json_ counterparts, except that they return the result in binary JSONB format:

select jsonb_insert('[11,12]', '$[#]', 13) -> 2;
select jsonb_replace('{"name":"alice"}', '$.name', 'bob') -> '$.name';
select jsonb_set('{"name":"alice"}', '$.city', 'berlin') -> '$.city';

13
"bob"
"berlin"

The same goes for the jsonb_patch and jsonb_remove functions:

select jsonb_patch(
  '{"name":"alice"}',
  '{"name":"bob", "city":"berlin"}'
) -> '$.city';

select jsonb_remove('[11,12,13]', '$[1]') -> 1;

"berlin"
13

And the jsonb_group_array and jsonb_group_object functions:

select
  department,
  json(jsonb_group_array(name)) as names
from employees
group by department;

┌────────────┬──────────────────────────────────┐
│ department │              names               │
├────────────┼──────────────────────────────────┤
│ hr         │ ["Diane","Bob"]                  │
│ it         │ ["Emma","Grace","Henry","Irene"] │
│ sales      │ ["Cindy","Dave"]                 │
└────────────┴──────────────────────────────────┘

There are no jsonb_ counterparts for the following functions:

json_array_length
json_error_position
json_type
json_valid
json_quote
json_each
json_tree

json_valid function

The new json_valid(X,Y) function return 1 if the argument X is well-formed JSON, or returns 0 if X is not well-formed.

The optional Y parameter is an integer bitmask that defines what is meant by "well-formed". The following bits of Y are currently defined:

  • 0x01 → The input is text that strictly complies with canonical RFC-8259 JSON, without any extensions.
  • 0x02 → The input is text that is JSON with JSON5 extensions.
  • 0x04 → The input is a BLOB that superficially appears to be JSONB.
  • 0x08 → The input is a BLOB that strictly conforms to the internal JSONB format.

The most useful Y value is 6 (0x02 | 0x04), which checks if X is JSON5 text or JSONB.

If Y is omitted, it defaults to 1 (X is strictly conforming RFC-8259 JSON text without any extensions). This makes the one-argument version of json_valid compatible with older versions of SQLite, prior to the addition of support for JSON5 and JSONB.

Some examples:

.mode line
select json_valid('{"x":42}');
select json_valid('{x:42}');
select json_valid('{x:42}', 6);
select json_valid(null);

json_valid('{"x":42}') = 1
json_valid('{x:42}') = 0
json_valid('{x:42}', 6) = 1
json_valid(null) =

Performance considerations

Most JSON functions do their internal processing using JSONB. So if the input is text, they first translate the input text to JSONB. If the input is already in the JSONB format, no translation is needed, so this step can be skipped, and performance is better.

For this reason, when an argument to a JSON function is supplied by another JSON function, it is usually more efficient to use the jsonb_ variant of the function used as the argument.

... json_insert(A, '$.b', json(C)) ...    ← Less efficient
... json_insert(A, '$.b', jsonb(C)) ...   ← More efficient

The aggregate JSON SQL functions are an exception to this rule. These functions all do their processing using text instead of JSONB. So for the aggregate JSON SQL functions, it is more efficient to pass arguments using json_ functions rather than jsonb_ functions.

... json_group_array(json(A))) ...    ← More efficient
... json_group_array(jsonb(A))) ...   ← Less efficient

And a few other things

See the full release notes for details.

──

Interactive examples in this post are powered by codapi — an open source tool I'm building. Use it to embed live code snippets into your product docs, online course or blog.

★ Subscribe to keep up with new posts.