Исполняемые доки SQLite: JSON в SQLite

Q: Как достать значение из JSON-колонки в SQLite?

Через json_extract(column, '$.path') или короткий оператор ->>. Например, SELECT data ->> '$.name' FROM users вернёт поле name из JSON-документа в колонке data. В путях $ — это корень, .field — обращение к ключу объекта, [i] — индекс массива.

Q: Можно ли построить индекс по полю внутри JSON?

Да, через индекс по выражению: CREATE INDEX idxuseremail ON users(json_extract(data, '$.email')). Если в WHERE использовать ровно то же выражение — индекс подхватится. Когда поле дёргают часто, чище получается вариант с generated-колонкой и обычным индексом по ней.

Q: Чем отличаются операторы -> и ->> в SQLite?

-> возвращает JSON-значение (строки приходят в кавычках, как валидный JSON), а ->> — обычное SQL-значение (текст или число, без кавычек). Для вывода или сравнения в WHERE берите ->>, а -> удобен, когда дальше идёт ещё один JSON-вызов по цепочке.

В SQLite нет типа JSON — и это нормально

В SQLite отсутствует отдельный тип данных для JSON. Документ хранится в обычной колонке TEXT, а набор встроенных функций — известный как расширение JSON1 — умеет его разбирать, читать и изменять. JSON1 уже встроено в любую современную сборку SQLite, так что ставить ничего не нужно.

Идея простая: храните документ как текст, а заглядывать внутрь поручайте функциям.

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

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

Две строки, в каждой — JSON-документ в обычной текстовой колонке. Теперь нужно научиться доставать оттуда значения.

Извлечение полей: json_extract и оператор ->>

json_extract(column, path) достаёт значение из JSON-документа. Путь всегда начинается с $ (корень документа), для ключей объекта используется .field, а для индексов массива — [i].

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

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

Писать json_extract(data, '$.name') в каждом запросе быстро надоедает, поэтому в SQLite есть два удобных оператора:

-> возвращает значение в JSON-виде (строки приходят вместе с кавычками).
->> возвращает обычное SQL-значение (текст или число, без кавычек).

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json вернёт "Ada" (это всё ещё JSON), а name_text — просто Ada. Оператор ->> берите, когда значение нужно сравнить или показать пользователю. А -> — когда результат пойдёт дальше на вход другой JSON-функции.

Фильтрация по полям JSON

Раз уж мы умеем доставать значения, то и фильтровать по ним сможем. Выражение пишем в WHERE так же, как и любое другое:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

Работает, но на таблице любого приличного размера это будет тормозить — для проверки условия SQLite приходится парсить JSON в каждой строке. Чуть ниже починим это с помощью индекса.

Собираем JSON: json_object и json_array

В обратную сторону тоже можно — конструировать JSON прямо в запросе:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

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

json_object('k1', v1, 'k2', v2, ...) собирает объект, а json_array(v1, v2, ...) — массив. Удобно, когда нужно собрать ответ для API прямо в SQL, и эти функции спокойно вкладываются друг в друга:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

Изменение JSON: json_set, json_insert, json_replace

В SQLite есть три близкие по смыслу функции, которые меняют JSON-документ и возвращают его новую версию:

json_set(doc, path, value) — записывает значение по пути: создаёт его, если такого пути ещё нет, и перезаписывает, если он уже существует.
json_insert(doc, path, value) — добавляет значение только в том случае, если по этому пути ещё ничего нет.
json_replace(doc, path, value) — наоборот, обновляет значение только если путь уже существует.

Важный момент: эти функции не меняют документ на месте — они возвращают новый JSON. Поэтому результат обычно сохраняют обратно через UPDATE:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

Обратите внимание: json_set умеет принимать сразу несколько пар «путь — значение» в одном вызове. А чтобы убрать ключ, используйте json_remove(doc, path).

Разворачиваем JSON-массив через json_each

json_each — это табличная функция: она берёт JSON-массив (или объект) и возвращает по строке на каждый элемент. Благодаря этому задача «найти пользователей с тегом admin», которая на чистом SQL выглядит громоздко, превращается в обычный JOIN:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

Каждая строка из users соединяется с элементами своего массива tags. json_each отдаёт удобные колонки: key, value, type, fullkey. Её родственник json_tree обходит документ рекурсивно, заходя в каждый вложенный узел — пригодится, когда структура документа заранее неизвестна.

Индексы по JSON-полям в SQLite

Запрос WHERE data ->> '$.active' = 1 выше работает, но SQLite приходится парсить каждую строку, чтобы проверить условие. Для полей, по которым вы ищете часто, заведите индекс по выражению:

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

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

Индекс должен использовать ровно то же выражение, что и запрос. Если в индексе у вас json_extract(data, '$.email'), а в запросе — data ->> '$.email', совпадения не будет, и индекс просто останется лежать без дела. Выберите один вариант и придерживайтесь его.

Для полей, по которым запросы идут постоянно, удобнее завести генерируемый столбец:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

email для тех, кто пишет запросы, выглядит как самый обычный столбец, но при этом автоматически синхронизируется с JSON.

Проверка корректности JSON

json_valid(text) возвращает 1, если текст парсится как JSON, и 0 в противном случае. В связке с ограничением CHECK это позволяет отсекать кривые данные ещё на этапе записи:

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

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

Первая вставка пройдёт, а вторая упадёт с ошибкой ограничения. Без такой проверки кривой JSON будет тихо лежать в таблице, пока через пару месяцев какой-нибудь вызов json_extract не рванёт на ровном месте.

JSON и JSONB: в чём отличия

Начиная с SQLite 3.45 появилось двоичное представление — JSONB. Это те же данные, но заранее распарсенные в компактный бинарный формат, чтобы функции не разбирали JSON заново при каждом обращении. Семейство функций jsonb_* (jsonb_extract, jsonb_set, jsonb_object и так далее) возвращает JSONB вместо текста, а к колонкам с JSONB применимы те же операторы, что и к обычному JSON.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

Простой JSON (текст) удобен, когда документы должны быть читаемыми глазами в дампах и легко поддаваться осмотру. JSONB стоит брать тогда, когда таблица большая, к ней часто обращаются, и накладные расходы на парсинг реально вылезают в профайлере. Не переключайтесь на JSONB по умолчанию — читаемость обычного JSON очень выручает при отладке.

Когда JSON — правильный выбор

Колонки с JSON хорошо себя показывают, когда:

Структура меняется от строки к строке (события, журналы аудита, входящие вебхуки от интеграций).
Вы кэшируете ответ внешнего API и хотите сохранить его как есть.
Поле запрашивается редко и почти никогда не используется в фильтрах.

И плохо подходят, когда:

JSON используется, чтобы не проектировать схему. Если у всех строк одни и те же поля — это колонки, а не JSON.
По значению часто нужно фильтровать или джойнить. Обычная колонка с индексом всегда обгонит обращение по JSON-пути.
Напрашиваются внешние ключи. У JSON нет реляционной целостности.

Золотая середина — комбинировать оба подхода: скалярные колонки для полей, по которым идут запросы и ограничения, и рядом колонка JSON для «длинного хвоста» переменных данных.

Что дальше: полнотекстовый поиск

JSON даёт гибкость на стороне хранения. На следующей странице разберём FTS5 — встроенный в SQLite движок полнотекстового поиска, который умеет нормальный поиск по тексту с ранжированием и подсветкой, и это сильно мощнее, чем LIKE.

Часто задаваемые вопросы

В каком виде SQLite хранит JSON?

Отдельного типа под JSON в SQLite нет — данные лежат как обычный TEXT. Работу с ним обеспечивает встроенное расширение JSON1 (включено по умолчанию начиная с версии 3.38): функции json_extract, json_set, json_each и другие парсят этот текст и работают с ним как с JSON. С версии 3.45 появился ещё и бинарный формат JSONB — он быстрее, если к одному документу обращаются часто.

Как достать значение из JSON-колонки в SQLite?

Через json_extract(column, '$.path') или короткий оператор ->>. Например, SELECT data ->> '$.name' FROM users вернёт поле name из JSON-документа в колонке data. В путях $ — это корень, .field — обращение к ключу объекта, [i] — индекс массива.

Можно ли построить индекс по полю внутри JSON?

Да, через индекс по выражению: CREATE INDEX idx_user_email ON users(json_extract(data, '$.email')). Если в WHERE использовать ровно то же выражение — индекс подхватится. Когда поле дёргают часто, чище получается вариант с generated-колонкой и обычным индексом по ней.

Чем отличаются операторы -> и ->> в SQLite?

-> возвращает JSON-значение (строки приходят в кавычках, как валидный JSON), а ->> — обычное SQL-значение (текст или число, без кавычек). Для вывода или сравнения в WHERE берите ->>, а -> удобен, когда дальше идёт ещё один JSON-вызов по цепочке.

Связанные концепции

JSON в SQLite: json_extract, json_set и json_each