JSON in SQLite: columns, functions, JSONB and indexes

JSON support arrived in stages, and the version you are running decides which of the following applies:

3.38.0  (2022-02-22)  JSON functions built in by default; -> and ->> operators added
3.45.0  (2024-01-15)  JSONB, the binary on-disk format, introduced

Check what you have with:

SELECT sqlite_version();

Before 3.38.0 the JSON functions lived in an optional extension called JSON1, which had to be compiled in. On anything current you do not need to install or enable anything.

You can write JSON as the declared type, and it is good documentation, but SQLite applies TEXT affinity to it and does not validate anything:

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

INSERT INTO events (payload) VALUES ('{"user_id": 7, "type": "signup"}');
INSERT INTO events (payload) VALUES ('this is not json at all');  -- accepted

That second insert succeeds. If you want the database to reject nonsense, say so explicitly with a CHECK constraint:

CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  payload TEXT NOT NULL CHECK (json_valid(payload))
);

There are three ways to pull a value out, and the difference between them matters:

SELECT
  json_extract(payload, '$.user_id') AS a,   -- SQL value: 7
  payload -> '$.user_id'             AS b,   -- JSON:      7  (as JSON text)
  payload ->> '$.user_id'            AS c    -- SQL value: 7
FROM events;

->> and json_extract give you a normal SQL value: an integer, a real, or text. -> gives you a JSON representation, which is what you want when you are extracting a nested object or array to pass to another JSON function.

The trap is comparisons. A JSON string value carries its quotes, so this returns nothing:

-- wrong: '"signup"' (JSON) is never equal to 'signup' (SQL text)
SELECT * FROM events WHERE payload -> '$.type' = 'signup';

-- right
SELECT * FROM events WHERE payload ->> '$.type' = 'signup';

Path syntax is the usual thing: $.a.b for nested objects, $.items[0] for array elements, and $.items[#-1] for the last element of an array.

These two turn JSON into rows, which is what makes JSON in SQLite genuinely useful rather than just storable. json_each walks one level, json_tree walks the whole structure recursively:

-- one row per tag, joined back to the event
SELECT e.id, t.value AS tag
FROM events e, json_each(e.payload, '$.tags') t;

-- find every event that has a given tag anywhere in the document
SELECT DISTINCT e.id
FROM events e, json_tree(e.payload) t
WHERE t.value = 'urgent';

Both are table-valued functions, so they go in the FROM clause and join like any other table. This is how you filter on array contents, which is otherwise awkward.

A query that filters on json_extract will scan every row unless you give it an index. SQLite supports indexes on expressions, which is exactly what is needed:

CREATE INDEX idx_events_user
  ON events (json_extract(payload, '$.user_id'));

-- uses the index
SELECT * FROM events WHERE json_extract(payload, '$.user_id') = 7;

Confirm the planner is actually using it rather than assuming, with EXPLAIN QUERY PLAN in front of your SELECT. If the output says SCAN rather than SEARCH, the index is not being used.

SQLite 3.45.0, released on 15 January 2024, added JSONB: the parsed internal representation of JSON, stored on disk as a BLOB rather than re-parsed from text on every read.

-- store the binary form
CREATE TABLE events (id INTEGER PRIMARY KEY, payload BLOB);
INSERT INTO events (payload) VALUES (jsonb('{"user_id": 7}'));

-- read it back as text when you need to see it
SELECT json(payload) FROM events;

-- the extraction functions work on both forms
SELECT payload ->> '$.user_id' FROM events;

Two things worth being clear about:

• It is not human-readable. Select the column directly and you get a blob. Wrap it in json() to get text back.
• It is not PostgreSQL's JSONB.The name is shared, the format is not, and SQLite's documentation describes it as an internal format rather than an interchange one. Do not move the bytes between the two databases.

Use it when a column is read far more often than it is written and the documents are large enough for parsing to cost something. For small payloads, plain text is simpler and the difference will not be measurable.

The functions work in both directions. json_object builds an object per row and json_group_array aggregates rows into an array:

SELECT json_group_array(json_object('id', id, 'name', name)) AS users
FROM users;

-- [{"id":1,"name":"Ada"},{"id":2,"name":"Grace"}]

This is also how you nest a child collection inside a parent row, returning a whole object graph from a single query instead of running one query per parent:

SELECT json_object(
         'id', u.id,
         'name', u.name,
         'orders', (SELECT json_group_array(json_object('id', o.id, 'total', o.total))
                    FROM orders o WHERE o.user_id = u.id)
       ) AS user
FROM users u;

You can try any of these against your own database in our free browser-based SQLite viewer, which runs read-only queries with nothing to install.