Does SQLite support JSON?

Yes. SQLite has built-in JSON functions (originally the JSON1 extension, now part of core). You store JSON in a TEXT or JSONB column and query it with functions like json_extract and operators like -> and ->>.

How do I extract a value from a JSON column in SQLite?

Use json_extract(column, '$.path') or the arrow operators — data -> '$.field' returns it as JSON and data ->> '$.field' returns it as a plain scalar. Use ->> in WHERE and GROUP BY clauses since it yields a comparable value.

How do I query elements of a JSON array in SQLite?

Use json_each, a table-valued function that expands an array into rows. Join it with your table, e.g. FROM events, json_each(events.data, '$.tags'), and each element's value becomes a row you can filter or aggregate.

Can I index a JSON field in SQLite?

Yes, with an expression index: CREATE INDEX idx ON t (data ->> '$.field'). Queries that filter on that same expression can then use the index instead of scanning every row.

What is JSONB in SQLite?

JSONB (added in SQLite 3.45) is a binary JSON format you can store instead of text. It avoids re-parsing JSON on each function call, so JSON queries run faster. The JSON functions accept both text and JSONB, so you can adopt it transparently for frequently queried columns.

SQLite JSON Functions: Store and Query JSON with SQL

SQLite speaks JSON

You don't need MongoDB or Postgres JSONB to store and query JSON — SQLite, the most deployed database in the world, has full JSON support built in (the JSON1 functions, now part of core SQLite). You store JSON in an ordinary TEXT (or JSONB) column and use SQL functions to reach inside it. For local apps, mobile, and edge/embedded workloads, this is a document store hiding in a relational database. It's the SQLite counterpart to querying JSON with DuckDB.

Storing JSON

There's no dedicated JSON column type in classic SQLite — you use TEXT and, optionally, the json() function to validate on insert:

sql

CREATE TABLE events (id INTEGER PRIMARY KEY, data TEXT);

INSERT INTO events (data) VALUES
  (json('{"user":{"name":"Ada","country":"US"},"amount":42.50,"tags":["a","b"]}'));

Wrapping the literal in json() parses and minifies it and throws if it's malformed — a cheap validation guard on write.

Extracting values: json_extract and the arrow operators

json_extract(column, path) pulls a value out using a JSON path ($ is the root):

sql

SELECT
  json_extract(data, '$.user.name')    AS name,
  json_extract(data, '$.amount')       AS amount,
  json_extract(data, '$.tags[0]')      AS first_tag
FROM events;

SQLite also supports the arrow operators (borrowed from Postgres and DuckDB):

`->` returns the value as JSON (quoted strings stay quoted).
`->>` returns the value as text/SQL (unquoted scalar).

sql

SELECT data ->> '$.user.name'    AS name,        -- Ada  (plain text)
       data ->  '$.tags'         AS tags_json     -- ["a","b"]  (still JSON)
FROM events
WHERE data ->> '$.user.country' = 'US';           -- filter on a nested field

->> is what you want in WHERE and GROUP BY clauses because it yields a comparable scalar.

Exploding arrays with json_each

json_each is a table-valued function that turns a JSON array into rows — the SQLite equivalent of UNNEST. Use it in a join to iterate array elements:

sql

-- one row per tag across all events, then count them
SELECT tag.value AS tag, COUNT(*) AS n
FROM events, json_each(events.data, '$.tags') AS tag
GROUP BY tag.value;

json_each exposes value, key, and type columns for each element, so you can filter and aggregate JSON arrays with ordinary SQL.

Building JSON in queries

The inverse — assembling JSON from rows — uses the constructor and aggregate functions:

sql

SELECT json_object('name', 'Ada', 'active', json('true'));  -- {"name":"Ada","active":true}
SELECT json_array(1, 2, 3);                                  -- [1,2,3]

-- aggregate rows into a JSON array
SELECT json_group_array(json_extract(data, '$.user.name')) FROM events;
-- ["Ada","Ravi",...]

json_group_array and json_group_object are aggregates that collapse many rows into a single JSON value — handy for returning a nested result from a flat table.

Indexing JSON fields

Querying ->> '$.user.country' on every row is a scan. For a field you filter on often, create an expression index so SQLite can use it:

sql

CREATE INDEX idx_country ON events (data ->> '$.user.country');

Now the earlier WHERE data ->> '$.user.country' = 'US' can use the index instead of scanning — the same typed-column-vs-JSON trade-off discussed in JSON in databases: index the fields you query, leave the rest as flexible JSON.

JSONB: the faster binary form

Recent SQLite (3.45+) added JSONB, a binary representation you can store instead of text. It skips re-parsing the JSON on every function call, so json_extract and friends run faster on JSONB columns. Store it with the jsonb() function; the query functions accept both text and JSONB transparently. For hot JSON columns it's a low-effort speedup.

When to use SQLite JSON

Great for semi-structured fields alongside relational data, local-first and mobile apps, config, and prototypes that might grow a document-shaped column.
Reach for a real document DB only when JSON *is* your primary model at large scale. For most apps, a JSON column in SQLite (or Postgres) is enough and keeps everything in one queryable place.

For the bigger picture see JSON in databases, Postgres JSONB, and querying JSON with DuckDB. To inspect a column's contents, format the JSON.