skip to content

In SQLite, how do you store and query a JSON settings value, and what changes when you store it as JSONB instead of text?

level: middleimportance: should knowfreq 40%

answer

  1. no JSON column type
  2. -> keeps JSON, ->> gives SQL
  3. jsonb_ prefix returns binary
  4. a BLOB only SQLite reads
  5. 3.45.0, and json_set undoes it

basics

~20 s

SQLite has no JSON type: it stores JSON as text and queries it with built-in functions and the -> and ->> operators. Since 3.45.0 it can also store JSONB, a binary BLOB those functions read without re-parsing.

solid answer

~50 s

SQLite keeps JSON in an ordinary column as text, and its JSON functions have been built in since 3.38.0; before that they were an opt-in compile option. `settings -> '$.notify'` returns the selected part as JSON text, `settings ->> '$.notify.sound'` returns an SQL value (JSON `true` comes back as integer 1), and `json_extract()` returns JSON for objects and arrays but SQL values for scalars. Since 3.45.0, `jsonb()` and the `jsonb_` functions produce **JSONB**, SQLite's internal binary form stored as a BLOB: every JSON function accepts it, skips the parser and so runs faster, and it is slightly smaller. Lookups are still O(N), the format is not PostgreSQL's, and it is meant for SQLite alone. Two traps: `json_set()` on a JSONB value returns text, so use `jsonb_set()` to keep the column binary, and a library older than 3.45.0 does not understand JSONB.

code

sql · 11 lines
sql
CREATE TABLE prefs(account_id INTEGER PRIMARY KEY, settings BLOB NOT NULL);
INSERT INTO prefs VALUES (1, jsonb('{"theme":"dark","notify":{"sound":true}}'));

SELECT typeof(settings), json(settings) FROM prefs;          -- blob | {"theme":"dark","notify":{"sound":true}}
SELECT settings -> '$.theme', settings ->> '$.notify.sound' FROM prefs;  -- "dark" | 1

UPDATE prefs SET settings = jsonb_set(settings, '$.theme', 'light');  -- stays a blob
UPDATE prefs SET settings = json_set(settings, '$.theme', 'dark');    -- becomes text

CREATE INDEX prefs_theme ON prefs(settings ->> '$.theme');
SELECT account_id FROM prefs WHERE settings ->> '$.theme' = 'dark';

go deeper

for a junior

Recall that SQLite stores JSON in ordinary columns and reads it with functions such as json_extract and the -> and ->> operators.

for a middle

Explain what -> and ->> each return, why JSONB is faster but still O(N), and how the json_ and jsonb_ prefixes decide what a write leaves behind.

for a senior

Show the pitfalls you would catch in review: json_set quietly turning JSONB back into text, comparisons against -> output, expression indexes that a differently written query misses, and older bundled libraries.

for a principal

Weigh adopting JSONB as a team default: faster reads and smaller files against an SQLite-only format, a 3.45.0 floor and the discipline of using jsonb_ writers everywhere.

## No JSON type, just functions SQLite stores only five kinds of value: NULL, integers, floating-point numbers, text and BLOBs. Backward compatibility rules out a new storage class, so there is no JSON column type. A settings document is kept in an ordinary column, as text, and **JSON functions** interpret it on demand. They read RFC 8259 JSON and, since 3.42.0, JSON5 extensions, but always generate canonical JSON. The functions became built-ins in 3.38.0, the same release that added the `->` and `->>` operators. Before that they were an extension included only when the library was compiled with `SQLITE_ENABLE_JSON1`; since then they can be left out only with `SQLITE_OMIT_JSON`. A phone whose bundled SQLite predates 3.38.0 may therefore have no JSON support at all. ## Reading values out For a column holding `{"theme":"dark","notify":{"sound":true}}`: | Expression | Returns | Result | |---|---|---| | `settings -> '$.theme'` | JSON text of the selected part | `"dark"`, quotes included | | `settings ->> '$.theme'` | the SQL value | `dark` | | `settings ->> '$.notify.sound'` | the SQL value | integer `1` | | `settings -> '$.notify'` | JSON text | `{"sound":true}` | | `json_extract(settings, '$.theme')` | SQL value for a scalar, JSON for an object or array | `dark` | The difference bites in `WHERE` clauses. `settings -> '$.theme' = 'dark'` is false, because the left side is the JSON string with its quotes; `settings ->> '$.theme' = 'dark'` is true. The right-hand side of either operator may be a full path such as `'$.notify.sound'` or a bare label such as `'theme'`. ## JSONB: what it is and what it is not Since 3.45.0, SQLite can store its internal parse-tree representation of JSON on disk as a BLOB, which it calls **JSONB**. - **Faster to use.** Every function that accepts JSON text also accepts JSONB and skips the JSON parser, potentially several times faster for repeated reads and updates. - **Slightly smaller** than the same document as text. - **Still O(N).** SQLite's JSONB makes no claim of constant-time lookup; finding a key still walks the value. - **Not PostgreSQL's format.** The name was borrowed but the two are not binary compatible. - **Internal to SQLite.** The documentation says applications should not use JSONB outside SQLite or try to reverse-engineer it. Send text JSON over the network. - **Opaque to older libraries.** A library before 3.45.0 does not understand JSONB. ## Writing and updating Functions whose names start with `json_` return text; those starting with `jsonb_` return JSONB. That naming rule is the main trap: 1. Insert with `jsonb('...')` to store binary. 2. Update with `jsonb_set()`, `jsonb_insert()`, `jsonb_replace()` or `jsonb_remove()`. Using `json_set()` returns text, so the column silently becomes text again; nothing breaks, but the speed advantage is lost. 3. Render a JSONB value for display or export with `json(settings)`; a plain `SELECT settings` returns raw bytes. 4. Validate with `json_valid()`, whose optional second argument, added in 3.45.0, chooses whether text JSON, JSON5 or JSONB counts as well-formed. Malformed JSONB, for instance a hand-made BLOB, never causes a memory error, but it can produce nonsense answers, and how it is handled may change between releases. Treat JSONB as opaque and let SQLite generate it. ## Indexing a JSON field The JSON functions are deterministic, so they may appear in an **expression index**: `CREATE INDEX prefs_theme ON prefs(settings ->> '$.theme')`. A query that writes the same expression, `WHERE settings ->> '$.theme' = 'dark'`, is answered from the index; one written with `json_extract(settings, '$.theme')` is not matched and scans the table. The matching rules belong with the lesson on SQLite's planner; the point here is to choose one spelling and use it everywhere. Whether a settings document should be JSON at all, rather than columns or rows, is a schema-design choice covered with the material on attribute-value modelling. ## Version traps on phones - JSON functions and `->`/`->>`: built in from 3.38.0. - JSON5 input: 3.42.0. - JSONB and the `jsonb_` functions: 3.45.0. - Also in 3.45.0: a BLOB that is not JSONB but looks like JSON text had been accepted as text by mistake. The rewrite fixed that by accident and broke apps relying on it; since 3.45.1 the legacy reading is officially supported again, but only as a fallback when no other interpretation works, and some BLOBs are valid both ways. Do not store text JSON in a BLOB.

  • In SQLite, why does settings -> '$.theme' = 'dark' evaluate to false when the theme is dark?
    `->` always returns JSON text, and a JSON string keeps its double quotes, so the left side is `"dark"` with quotes, which does not equal the SQL text `dark`. `->>` returns the SQL value instead, so `settings ->> '$.theme' = 'dark'` is true. `json_extract()` behaves like `->>` for scalars and like `->` for objects and arrays.
  • Can a SQLite expression index speed up WHERE settings ->> '$.theme' = 'dark'?
    Yes. The JSON functions are deterministic, so `CREATE INDEX prefs_theme ON prefs(settings ->> '$.theme')` is allowed, and the planner answers that query from the index. It must be the same expression, though: a query written with `json_extract(settings, '$.theme')` does not match that index and scans the table.

  • SQLite has a native JSON column type that validates every insert.
  • SQLite's JSONB gives constant-time key lookup like PostgreSQL's jsonb.
  • JSONB written by SQLite can be read directly by other databases.
  • json_set() keeps a JSONB column in its binary form.
  • -> and ->> return the same value for every path.
  • Every phone's SQLite has the JSON functions built in.

Assumes SQLite 3.53; JSON functions and the -> and ->> operators are built in since 3.38.0 and JSONB storage arrived in 3.45.0, so an older bundled library lacks them.