I like SQLite’s JSON functions because they let me keep a small amount of flexible data without immediately turning every property into a column. The trade-off is easy to miss: if I store JSON as text, SQLite has to parse that text before it can navigate the structure.

Since SQLite 3.45.0, there is another option. SQLite can persist its binary JSON representation, called JSONB, directly in a BLOB.

Here’s the idea: if the database is going to inspect the same JSON repeatedly, I can let SQLite store the representation it already wants to process instead of making it parse the text again.

JSONB is SQLite’s internal JSON representation

SQLite’s JSON implementation uses a parsed representation internally. JSONB makes that representation serializable, so it can live in a table and be fed back into JSON functions later.

A simple example looks like this:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload BLOB NOT NULL
);

INSERT INTO events (payload)
VALUES (jsonb('{"type":"signup","user":{"id":42}}'));

The jsonb() function accepts JSON and returns SQLite’s binary JSONB value. The important part is what I do afterward: I keep treating payload as JSON through SQLite’s JSON API.

SELECT
    json_extract(payload, '$.type') AS type,
    json_extract(payload, '$.user.id') AS user_id
FROM events;

Functions that accept JSON input generally accept either JSON text or JSONB, so existing extraction code often needs little or no change.

The benefit is less parsing, not magical lookup complexity

It is tempting to read “binary JSON” and assume indexed object lookups. That is not what SQLite JSONB promises.

Most JSONB operations still have O(N) complexity, like their text-JSON equivalents. The advantage is that SQLite does not need to parse the textual syntax first. JSONB also normally occupies somewhat less space than the equivalent JSON text.

That distinction matters when deciding whether a migration is worthwhile. If my query spends most of its time scanning a huge JSON document for a deeply nested value, JSONB does not turn that scan into O(1). It removes parsing overhead around the work.

For fields I filter or join on frequently, I still prefer normal columns or generated columns with indexes.

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload BLOB NOT NULL,
    event_type TEXT GENERATED ALWAYS AS (
        json_extract(payload, '$.type')
    ) STORED
);

CREATE INDEX events_event_type_idx ON events(event_type);

JSONB and relational indexing solve different problems, and they work well together.

Generate JSONB inside SQLite

I avoid constructing JSONB bytes in application code. SQLite explicitly treats the format as an internal representation, even though the on-disk format is documented and intended to remain compatible.

Instead, I pass JSON text or SQL values into SQLite and let its functions produce the BLOB.

INSERT INTO events (payload)
VALUES (
    jsonb_object(
        'type', 'purchase',
        'amount', 1999,
        'currency', 'USD'
    )
);

The same pattern applies when updating a document. JSON-producing functions have jsonb_ variants where a binary result is useful.

UPDATE events
SET payload = jsonb_set(payload, '$.processed', jsonb('true'))
WHERE id = 1;

Keeping construction inside the JSON API also means my application does not depend on SQLite’s binary encoding details.

JSONB is not PostgreSQL JSONB

The shared name is slightly dangerous. SQLite’s JSONB was inspired by PostgreSQL’s naming, but the two binary formats are not compatible.

I therefore would not send a SQLite JSONB BLOB to PostgreSQL, expose it as an API payload, or write it into a cache as if it were a portable JSON encoding. At application boundaries I convert back to ordinary JSON text.

SELECT json(payload)
FROM events
WHERE id = 1;

That gives the rest of the system a format it actually understands.

This boundary is useful architecturally too. JSONB can remain a database implementation detail while HTTP APIs, queues, files, and logs continue using normal JSON.

Validate data at the write boundary

A BLOB column does not automatically mean “valid JSONB.” SQLite’s dynamic typing still allows arbitrary BLOB data unless I constrain it.

For a table that should contain only JSONB, I can add a CHECK constraint using the two-argument form of json_valid():

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    body BLOB NOT NULL CHECK (json_valid(body, 8))
);

The 8 flag asks for strict validation of SQLite’s internal JSONB format. SQLite also supports other validation masks, including a faster superficial JSONB check.

There is a practical performance choice here. Strict validation examines the complete BLOB, while the superficial JSONB check is designed to be much faster. If every value is created by trusted SQLite JSONB functions, I may not need to pay for strict validation on every read. If arbitrary BLOBs can cross the write boundary, validation becomes much more valuable.

Do not manipulate the BLOB yourself

JSONB generated by SQLite is well-formed, but a BLOB can be modified like any other binary value. Feeding malformed JSONB to JSON functions can produce an error or nonsensical results depending on the malformed data and SQLite version.

So I treat JSONB as opaque:

application values
      ↓
SQLite jsonb_* functions
      ↓
opaque BLOB in the database
      ↓
SQLite JSON functions
      ↓
application values / JSON text

I do not splice bytes, patch offsets, or rely on the encoding layout. If I need to change a property, I use jsonb_set(), jsonb_patch(), or another documented JSON operation.

Migration can be incremental

One nice property of SQLite’s JSON functions is that text JSON and JSONB can coexist as inputs. That gives me room to migrate without rewriting every query first.

Suppose an existing table stores JSON text:

CREATE TABLE settings (
    id INTEGER PRIMARY KEY,
    value TEXT NOT NULL
);

Before changing anything, I check the SQLite runtime rather than assuming the application’s package version tells the whole story:

SELECT sqlite_version();

JSONB requires SQLite 3.45.0 or newer. Once every deployed runtime supports it, I can create a new table or deliberately migrate the storage representation.

For a controlled migration, I prefer a new column or table over casually changing a column’s meaning from text to binary. It makes rollback and mixed-version deployments easier to reason about.

ALTER TABLE settings ADD COLUMN value_jsonb BLOB;

UPDATE settings
SET value_jsonb = jsonb(value);

Then I can verify the converted data before switching application reads and eventually removing the old representation in a table rebuild.

Watch for legacy JSON stored as BLOB text

There is one historical edge case worth knowing. Older applications sometimes stored textual JSON in a BLOB, especially when data came from SQLite CLI helpers such as readfile().

SQLite 3.45.0 briefly stopped accepting those non-JSONB BLOBs as text JSON when the JSON implementation was rewritten. SQLite 3.45.1 restored the legacy behavior for compatibility.

I still would not design new code around that behavior. A BLOB containing JSON text can even be ambiguous with valid JSONB for some byte sequences. If a legacy database stores textual JSON as BLOBs, converting those values to actual TEXT or intentional JSONB makes the representation explicit.

Measure before converting everything

JSONB is most interesting when JSON parsing is real work in the workload: repeated extraction, updates, or transformations over stored documents.

For a tiny preferences object read once at startup, the difference may not matter. Converting every JSON column just because JSONB exists adds migration work and makes raw database inspection less convenient.

I benchmark the operations I actually perform. A useful test compares equivalent text and JSONB data using the same document shapes, row counts, paths, and queries. I also include database size and write cost rather than measuring only one extraction loop.

If frequently queried properties dominate the workload, indexes or schema changes can matter far more than switching the JSON encoding.

The practical takeaway

SQLite JSONB is a useful optimization because it lets the database persist JSON in a representation that avoids repeated text parsing. It can be somewhat smaller and faster to process, while the familiar JSON functions continue to work with it.

But I keep the boundary clear: JSONB is an opaque SQLite storage format, not a portable replacement for JSON and not PostgreSQL JSONB. It also does not turn JSON navigation into constant-time lookup.

In the end, I use JSONB when SQLite repeatedly works with stored JSON, keep important searchable fields indexable, validate untrusted binary inputs, and convert back to ordinary JSON at system boundaries. That gives me the performance benefit without letting a database-internal representation leak into the rest of the application.

Database SQLite JSON Performance