SQLite jsonb() Function
The SQLite jsonb(X) function returns the binary JSONB representation of JSON value X. Use it when you want JSON functions to operate on SQLite’s internal JSONB format instead of JSON text.
SQLite JSONB is an opaque SQLite-specific BLOB format. It is not compatible with PostgreSQL jsonb, and its internal bytes should not be parsed or constructed by applications. See SQLite JSONB: When and How to Use It for storage and performance guidance.
Syntax
jsonb(X)
X may be JSON text or a BLOB that appears to be SQLite JSONB. Invalid JSON text causes an error. If X is a BLOB that appears to be JSONB, jsonb() returns a copy after examining only the outermost element; it does not validate the full internal structure.
Return value
jsonb() returns a BLOB containing SQLite JSONB. JSONB was added in SQLite 3.45.0. JSONB conversion from text can avoid repeated parsing when the value is processed by other SQLite JSON functions.
To strictly check an untrusted BLOB’s JSONB structure, use json_valid(X, 8). The ordinary one-argument json_valid(X) checks canonical JSON text, not JSONB. SQLite’s JSON functions are built in by default starting in 3.38.0; older builds may need JSON1 enabled.
Examples
Convert JSON text to JSONB, then render the value back as JSON text:
WITH source(json_text) AS (
VALUES ('{"user":{"id":7,"roles":["reader","editor"]}}')
)
SELECT
typeof(jsonb(json_text)) AS storage_class,
json(jsonb(json_text)) AS json_text
FROM source;
storage_class json_text
------------- ---------------------------------------------
blob {"user":{"id":7,"roles":["reader","editor"]}}The returned BLOB is JSONB; json() converts it to minified JSON text for display or exchange.