SQLite JSONB: When and How to Use It

SQLite JSONB is a binary representation of JSON stored as a BLOB. It was added in SQLite 3.45.0. JSONB can reduce parsing and rendering work inside SQLite, but it is an internal SQLite format, not a portable JSON interchange format.

SQLite does not have a separate JSON storage class. JSON text is stored as TEXT; JSONB is stored as BLOB. SQLite JSONB is also not binary-compatible with PostgreSQL jsonb, despite the shared name.

You can run the examples that use jsonb(), jsonb_extract(), and json_each() in the SQLite SQL playground. It runs SQLite 3.49.1; the newer jsonb_each() and jsonb_tree() functions shown below require SQLite 3.51.0 or later.

Convert JSON text to SQLite JSONB

Use jsonb() when you want to store a JSONB value. typeof() shows its SQLite storage class:

SELECT typeof(jsonb('{"event":"login","user_id":42}')) AS storage_class;

storage_class
-------------
blob

A table can store JSONB in a BLOB column. This example validates that inserted values are well-formed SQLite JSONB:

CREATE TABLE event_log (
    event_id INTEGER PRIMARY KEY,
    payload BLOB NOT NULL CHECK (json_valid(payload, 8))
);

INSERT INTO event_log (payload)
VALUES (jsonb('{"event":"login","user_id":42,"tags":["web","account"]}'));

The 8 flag asks json_valid() to strictly validate the JSONB format. json_valid(X, flags) and SQLite JSONB require SQLite 3.45.0 or later.

Read JSONB with JSON functions

JSON functions accept either JSON text or SQLite JSONB. Scalar extraction returns ordinary SQLite values, so the same query works with either storage format:

SELECT
    json_extract(payload, '$.event') AS event_name,
    json_extract(payload, '$.user_id') AS user_id
FROM event_log;

event_name  user_id
----------  -------
login       42

If you need a JSON text value for output or interchange, convert it with json():

SELECT json(payload) AS json_text
FROM event_log;

For a selected object or array, jsonb_extract() returns JSONB while json_extract() returns JSON text. Both return the same SQLite scalar for a selected string, number, boolean, or null:

SELECT
    typeof(jsonb_extract(payload, '$.tags')) AS storage_class,
    json(jsonb_extract(payload, '$.tags')) AS tags_json
FROM event_log;

storage_class  tags_json
-------------  -----------------
blob           ["web","account"]

See the SQLite JSON function reference for the full list of JSON and JSONB functions.

SQLite 3.51.0 adds jsonb_each() and jsonb_tree(). They behave like json_each() and json_tree(), but return nested objects and arrays as JSONB values.

Performance and format limits

SQLite JSONB stores SQLite’s internal parse-tree representation. Passing JSONB between JSON functions avoids parsing text into that representation each time and can reduce storage size. Performance depends on the workload; JSONB does not make most lookups constant-time. Most JSONB operations remain O(N), where N is the size of the JSON value.

Treat SQLite JSONB as opaque. Do not inspect or construct its bytes yourself, and do not send it to another database expecting that database to understand the format. Convert it to JSON text with json() when exporting or exchanging JSON.

Malformed JSONB supplied as a BLOB may produce an error or an incorrect result. Use SQLite-generated JSONB or validate untrusted BLOBs with json_valid(blob, 8) before processing them.

Choose JSON text or JSONB

• Keep JSON as TEXT when the stored representation should be directly readable or exchanged with other systems.
• Consider JSONB when the application repeatedly processes JSON inside SQLite and can keep the value in SQLite’s own format.
• Use ordinary relational columns and indexes for fields that need frequent filtering, sorting, or joins; JSONB does not provide constant-time path lookup.

JSON functions are built into SQLite by default starting with 3.38.0. SQLite JSONB begins with 3.45.0, while jsonb_each() and jsonb_tree() require 3.51.0. See the SQLite JSON documentation for current function behavior and version details.