Published: 2026-05-06

SQLite JSON: How json1 Turns SQLite into a Document Store

SQLite has no formal JSON type and no document API. And yet, with the json1 extension that ships on by default since 3.38, SQLite handles document workloads that used to require running MongoDB next to your application. For embedded and edge use cases, it's often the better choice.

Quick Answers

Q: Does SQLite support JSON?

A: Yes — the json1 extension is built into SQLite and enabled by default since 3.38 (2022). It provides JSON parsing, json_extract, the -> and ->> operators, json_each, json_tree, plus constructors and mutators.

Q: Does SQLite have a JSON data type?

A: There is no formal JSON type — SQLite stores JSON text in TEXT columns. Since 3.45 (2024), SQLite also has a JSONB binary representation that lives in a BLOB column and is read with jsonb_extract / jsonb_set / other jsonb_* functions. JSONB is roughly 5–10% smaller than JSON text and faster to access.

Q: How do I index JSON in SQLite?

A: Use a generated column that extracts the path with json_extract, then index that column — or skip the column entirely and create an expression index directly on json_extract(data, '$.email'). SQLite has supported expression indexes since 3.9.

Q: Can SQLite replace MongoDB?

A: For embedded, single-process, or single-server applications, yes — JSON storage, path queries, generated-column indexes, and full ACID in a zero-configuration single file. For distributed multi-writer document stores, MongoDB still wins.

A Compact History of SQLite JSON

• SQLite 3.9 (2015) — json1 extension introduced as an opt-in compile-time module.
• 3.38 (2022) — json1 built in and enabled by default. The -> and ->> operators borrowed from PostgreSQL.
• SQLite 3.45 (2024-01-15) — JSONB binary format. Documents are stored as a BLOB in a pre-parsed binary representation, roughly 5–10% smaller than JSON text and faster to access since no re-parsing is required.

As of May 2026, every SQLite release in active distribution ships json1 by default; JSONB is current as of 3.45+. Every SQLite you encounter — in iOS, Android, browsers, embedded devices, and most desktop apps — supports JSON out of the box. You don't need to install anything. The authoritative reference is SQLite JSON Functions And Operators.

JSON Storage in SQLite

SQLite stores JSON as TEXT. There is no formal type, but the convention is well-established:

• For JSON text, declare the column as TEXT (or skip the type — SQLite is dynamically typed).
• Add a CHECK (json_valid(data)) constraint if you want validation on insert.
• For pre-parsed binary storage, store a BLOB produced by jsonb() (or any jsonb_* constructor) and read it back with jsonb_extract, jsonb_set, …. JSONB is "internal-only" per the SQLite docs — the format is stable enough to use, but not a wire-format spec.

Querying JSON: PostgreSQL-Style Operators

The -> and ->> operators are modelled on PostgreSQL and MySQL. SQLite extends them to accept JSONPath strings (e.g. '$.email') in addition to plain keys, and ->> returns SQL TEXT/INTEGER/REAL/NULL depending on the underlying JSON scalar (PostgreSQL always returns text). Everything else is familiar:

-- Extract as JSON (chainable)
SELECT data->'address'->'city' FROM customers;

-- Extract as scalar text (use this in WHERE)
SELECT * FROM customers WHERE data->>'$.email' = '[email protected]';

-- Functional form
SELECT * FROM customers
WHERE json_extract(data, '$.email') = '[email protected]';

-- Path existence
SELECT * FROM customers
WHERE json_extract(data, '$.phone') IS NOT NULL;

-- Validity
SELECT * FROM customers WHERE json_valid(data);

-- Shred a JSON array into rows (the SQLite OPENJSON)
SELECT o.id, j.value->>'sku', j.value->>'qty'
FROM orders o, json_each(o.items) j;

-- Walk every node in a document (recursive)
SELECT key, value FROM json_tree('{"a":1,"b":{"c":2}}');

json_each and json_tree are the two functions that distinguish SQLite from most other engines. Together they let you treat any JSON document as a virtual table you can join against.

Indexing JSON in SQLite

SQLite has no GIN-style document index. The pattern is generated-column-plus-index, identical to MySQL:

ALTER TABLE customers
  ADD COLUMN email TEXT
  GENERATED ALWAYS AS (json_extract(data, '$.email')) VIRTUAL;

CREATE INDEX idx_customers_email ON customers(email);

-- Or skip the column entirely and index an expression directly
CREATE INDEX idx_customers_email_expr
  ON customers(json_extract(data, '$.email'));

SQLite supports expression indexes since 3.9, so the second form works even on columns you can't ALTER. The query optimiser will use either index for queries that match the expression exactly. See Indexes On Expressions in the SQLite docs for the full rules.

When SQLite JSON Surprises You

• Performance. For documents under ~10 KB, SQLite's json_extract over TEXT is competitive with PostgreSQL's jsonb. JSONB (3.45+) closes the gap further.
• Atomicity. JSON updates participate in normal SQLite transactions — no separate document API to coordinate with the rest of your data.
• Backup. The whole document store is one file. cp data.db backup.db while the WAL is checkpointed and you have a snapshot.
• Replication. Tools like Litestream and LiteFS replicate the SQLite file to S3 / multi-region with very small latency — the missing piece that makes SQLite viable for production document workloads.

SQLite JSON vs MongoDB

For a single-process or single-server document workload, as of May 2026 SQLite + json1 is genuinely competitive with MongoDB — and dramatically simpler:

Pick SQLite + json1 when your application is single-process, edge, embedded, or runs on one server. The reduction in operational complexity is enormous and the document features you actually use are all present.

Pick MongoDB when you need many concurrent writers across multiple machines, you need horizontal sharding from day one, or your team's tooling investment is in MongoDB-native infrastructure.

The "SQLite is just for prototypes" trope is wrong. Expensify (BedrockDB), Tailscale, and Cloudflare D1 all run SQLite in production at serious scale. JSON support inside that footprint puts a real document store one CREATE TABLE away.

A Realistic Schema Pattern

CREATE TABLE events (
  id          INTEGER PRIMARY KEY,
  user_id     INTEGER NOT NULL,
  event_type  TEXT NOT NULL,
  occurred_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
  data        TEXT NOT NULL CHECK (json_valid(data)),
  event_name  TEXT GENERATED ALWAYS AS (json_extract(data, '$.name')) VIRTUAL
);

CREATE INDEX idx_events_user_time
  ON events (user_id, occurred_at DESC);
CREATE INDEX idx_events_name ON events (event_name);

-- Recommended PRAGMAs for production JSON workloads:
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;

Working with SQLite JSON in a SQL IDE

Most SQLite tools were designed before json1 shipped by default. They render JSON as a single line of escaped text and offer no path-aware filtering — you write json_extract calls by hand every time.

Jam SQL Studio recognises any TEXT column declared as JSON in MetaInfo and renders cells as collapsible trees. The filter chip exposes a json operator with sub-operators (has property, property =, property contains) that compile to json_extract, json_valid, and the ->> operator under the hood, with the json_valid(data) guard wrapped around the predicate so non-JSON rows don't break the query. A Peek popover scans the loaded rows to show every JSON path with prevalence — the same schema-discovery experience MongoDB Compass made famous, applied to a sub-megabyte embedded database. The same UI works against the JSONB binary format introduced in 3.45.

Last verified against sqlite.org documentation on 2026-05-13.