SQLite Has No JSON Type — and That's Fine
SQLite doesn't have a dedicated JSON column type. JSON goes into a regular TEXT column, and a set of built-in functions — collectively called the JSON1 extension — knows how to parse, query, and modify it. JSON1 ships with every modern SQLite build, so there's nothing to install.
The mental model: store the document as text, use functions to look inside it.
Two rows, each holding a JSON document in a plain text column. Now we need ways to reach into those documents.
Extracting Fields With json_extract and ->>
json_extract(column, path) pulls a value out of a JSON document. The path starts with $ (the root) and uses .field for object keys and [i] for array indices.
Writing json_extract(data, '$.name') everywhere gets old fast, so SQLite gives you two operators:
->returns a JSON-encoded value (strings come back with their quotes).->>returns a SQL value (text or number, no quotes).
name_json comes back as "Ada" (still JSON), name_text as Ada. Use ->> when you want a value to compare or display. Use -> when you're going to feed the result into another JSON function.
Filtering on JSON Fields
Once you can extract, you can filter. The expression goes in the WHERE clause like any other:
This works, but on a table of any size it's slow — every row has to be parsed to evaluate the predicate. We'll fix that with an index in a moment.
Building JSON: json_object and json_array
Going the other way, you can construct JSON inside a query:
json_object('k1', v1, 'k2', v2, ...) builds an object. json_array(v1, v2, ...) builds an array. They're handy for assembling API responses directly in SQL, and they'll happily nest:
Updating JSON: json_set, json_insert, json_replace
Three closely related functions modify a JSON document and return the new version:
json_set(doc, path, value)— set the path, creating it if missing, overwriting if it exists.json_insert(doc, path, value)— only insert if the path doesn't already exist.json_replace(doc, path, value)— only update if the path already exists.
The functions don't mutate in place — they return a new document, which you typically write back with UPDATE:
Note that json_set accepts multiple path/value pairs in one call. To remove a key, use json_remove(doc, path).
Expanding Arrays With json_each
json_each is a table-valued function: it takes a JSON array (or object) and returns one row per element. That turns "find users with the admin tag" — awkward in plain SQL — into a normal join:
Each row from users gets joined against the elements of its tags array. json_each exposes useful columns including key, value, type, and fullkey. Its sibling json_tree walks the entire document recursively, every nested node included — handy for searching documents of unknown shape.
Indexing JSON Fields
The WHERE data ->> '$.active' = 1 query above works, but SQLite has to parse every row to evaluate the predicate. For fields you query often, build an expression index:
The index has to use the exact same expression as your query. Mixing json_extract(data, '$.email') in the index with data ->> '$.email' in the query won't match, and the index will sit unused — pick one form and stick with it.
For fields you query constantly, a generated column reads better:
email looks like a normal column to query writers but stays in sync with the JSON automatically.
Validating JSON
json_valid(text) returns 1 if the text parses as JSON, 0 otherwise. Pair it with a CHECK constraint to refuse bad data at write time:
The first insert succeeds; the second fails with a constraint error. Without that check, malformed JSON sits quietly in the table until some json_extract call blows up months later.
JSON vs JSONB
Since SQLite 3.45, there's a binary representation called JSONB — the same data, pre-parsed into a compact binary form so functions don't re-parse on every call. The jsonb_* family of functions (jsonb_extract, jsonb_set, jsonb_object, ...) returns JSONB instead of text, and JSONB columns can be queried with the same operators.
Use plain JSON (text) when you want documents to be human-readable in dumps and easy to inspect. Reach for JSONB when a table is large, queried often, and the parsing overhead actually shows up in profiling. Don't switch by default — the readability of plain JSON is worth a lot during debugging.
When JSON Is the Right Call
JSON columns shine when:
- The shape varies from row to row (think event payloads, audit logs, integration webhooks).
- You're caching an external API response and want to keep it intact.
- A field is rarely queried and almost never filtered on.
They're a bad fit when:
- You're using JSON to dodge designing a schema. If every row has the same fields, those are columns.
- You need to filter or join on a value frequently. A real column with an index will outrun a JSON path lookup every time.
- You'd reach for foreign keys. JSON has no relational integrity.
The sweet spot is mixing the two: scalar columns for the fields that drive queries and constraints, a JSON column alongside for the long tail of variable data.
Next: Full-Text Search
JSON gives you flexibility on the storage side. The next page covers FTS5 — SQLite's full-text search engine — which gives you proper text search with ranking and highlighting, far beyond what LIKE can do.
Frequently Asked Questions
How does SQLite store JSON?
SQLite has no dedicated JSON type — JSON is stored as plain TEXT. The built-in JSON1 extension (compiled in by default since 3.38) provides functions like json_extract, json_set, and json_each that parse and operate on that text. Since 3.45 there's also a binary JSONB format for faster repeated access.
How do I query a JSON column in SQLite?
Use json_extract(column, '$.path') or the shorthand ->> operator. For example, SELECT data ->> '$.name' FROM users pulls the name field out of a JSON document stored in data. Paths use $ for the root, .field for object keys, and [i] for array indices.
Can I index a JSON field in SQLite?
Yes — create an expression index on the extracted path: CREATE INDEX idx_user_email ON users(json_extract(data, '$.email')). Queries that use the same expression in their WHERE clause will use the index. For frequently queried fields, a generated column with an index is often cleaner.
What's the difference between -> and ->> in SQLite?
-> returns a JSON value (still JSON-encoded — strings come back quoted), while ->> returns a SQL value (text or number, unquoted). Use ->> when you want the raw value for display or comparison; use -> when chaining further JSON operations.
