SQLite et JSON : json_extract, json_set et json_each

SQLite n'a pas de type JSON — et c'est très bien comme ça

SQLite n'offre pas de type de colonne JSON dédié. Le JSON se range tout simplement dans une colonne TEXT, et c'est un ensemble de fonctions intégrées — qu'on appelle collectivement l'extension JSON1 — qui se charge de l'analyser, de l'interroger et de le modifier. JSON1 est livrée avec toute version récente de SQLite, donc rien à installer.

Le modèle mental à retenir : on stocke le document sous forme de texte, et on utilise des fonctions pour aller fouiller à l'intérieur.

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;

Deux lignes, chacune contenant un document JSON dans une simple colonne texte. Il nous faut maintenant des moyens d'aller fouiller dans ces documents.

Extraire des champs avec json_extract et ->>

json_extract(column, path) récupère une valeur depuis un document JSON. Le chemin JSON commence toujours par $ (la racine) et utilise .champ pour les clés d'objet et [i] pour les index de tableau.

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;

Écrire json_extract(data, '$.name') partout devient vite pénible. Heureusement, SQLite propose deux opérateurs bien plus pratiques :

• -> renvoie une valeur encodée en JSON (les chaînes arrivent avec leurs guillemets).
• ->> renvoie une valeur SQL (texte ou nombre, sans guillemets).

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 renvoie "Ada" (toujours du JSON), tandis que name_text renvoie ada. Utilisez ->> quand la valeur doit être comparée ou affichée. Préférez -> lorsque vous comptez réinjecter le résultat dans une autre fonction JSON.

Filtrer sur les champs JSON

Dès qu'on sait extraire, on sait filtrer. L'expression se place dans la clause WHERE, comme n'importe quelle autre :

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 nom
FROM users
WHERE data ->> '$.active' = 1;

Ça fonctionne, mais sur une table de taille respectable, c'est lent : chaque ligne doit être parsée pour évaluer le prédicat. On va corriger ça avec un index dans un instant.

Construire du JSON : json_object et json_array

Dans l'autre sens, vous pouvez fabriquer du JSON directement dans une requête :

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, ...) construit un objet. json_array(v1, v2, ...) construit un tableau. Très pratiques pour assembler des réponses d'API directement en SQL, et ils s'imbriquent sans souci :

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

Mettre à jour du JSON : json_set, json_insert, json_replace

SQLite propose trois fonctions très proches pour modifier un document JSON et renvoyer la nouvelle version :

• json_set(doc, path, value) — affecte une valeur au chemin indiqué, en le créant s'il n'existe pas, ou en l'écrasant s'il existe déjà.
• json_insert(doc, path, value) — insère uniquement si le chemin n'existe pas encore.
• json_replace(doc, path, value) — met à jour uniquement si le chemin existe déjà.

Attention : ces fonctions ne modifient pas le document sur place. Elles renvoient un nouveau document, que l'on réécrit ensuite en base avec un 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 accepte plusieurs paires chemin/valeur dans un même appel. Pour supprimer une clé, on passe par json_remove(doc, path).

Déplier des tableaux avec json_each

json_each est une fonction qui renvoie une table : vous lui passez un tableau (ou un objet) JSON et elle te sort une ligne par élément. Du coup, « trouver les utilisateurs qui ont le tag admin » — un truc pénible à écrire en SQL classique — devient une simple jointure :

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';

Chaque ligne de users est jointe avec les éléments de son tableau tags. json_each expose des colonnes bien pratiques : key, value, type et fullkey. Son cousin json_tree parcourt le document de façon récursive, en incluant tous les nœuds imbriqués — idéal pour fouiller des documents dont on ne connaît pas la structure.

Indexer un champ JSON dans SQLite

La requête WHERE data ->> '$.active' = 1 vue plus haut fonctionne, mais SQLite doit analyser chaque ligne pour évaluer la condition. Pour les champs que vous interrogez souvent, créez plutôt un index d'expression :

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';

L'index doit reposer sur exactement la même expression que celle de votre requête. Si vous mélangez json_extract(data, '$.email') dans l'index et data ->> '$.email' dans la requête, ça ne matchera pas et l'index restera inutilisé — choisissez une forme et tenez-vous-y.

Pour les champs interrogés en permanence, une colonne générée se lit mieux :

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 ressemble à une colonne classique pour celui qui écrit les requêtes, mais reste synchronisée automatiquement avec le JSON.

Valider du JSON

json_valid(text) renvoie 1 si le texte est du JSON valide, 0 sinon. Combinez-le à une contrainte CHECK pour rejeter les données invalides dès l'écriture :

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');

Le premier INSERT passe ; le second échoue avec une erreur de contrainte. Sans cette vérification, du JSON malformé reste tranquillement dans la table jusqu'au jour où un json_extract finit par tout faire planter, des mois plus tard.

JSON ou JSONB : quelles différences ?

Depuis SQLite 3.45, il existe une représentation binaire appelée JSONB — les mêmes données, mais pré-analysées sous une forme binaire compacte, ce qui évite aux fonctions de re-parser le JSON à chaque appel. La famille de fonctions jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) renvoie du JSONB au lieu de texte, et les colonnes JSONB s'interrogent avec les mêmes opérateurs.

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;

Privilégiez le JSON en clair (texte) quand vous voulez que vos documents restent lisibles dans les dumps et faciles à inspecter. Passez au JSONB quand une table devient volumineuse, qu'elle est interrogée souvent, et que le coût du parsing apparaît vraiment dans vos profils. Ne changez pas par défaut : la lisibilité du JSON en clair vaut son pesant d'or au moment du débogage.

Quand le JSON dans SQLite est-il le bon choix ?

Les colonnes JSON brillent dans ces cas :

• La structure varie d'une ligne à l'autre (payloads d'événements, journaux d'audit, webhooks d'intégration).
• Vous mettez en cache une réponse d'API externe et vous voulez la conserver telle quelle.
• Un champ est rarement interrogé et quasiment jamais filtré.

À l'inverse, c'est un mauvais choix quand :

• Vous utilisez le JSON pour éviter de concevoir un schéma. Si toutes les lignes ont les mêmes champs, ce sont des colonnes.
• Vous avez besoin de filtrer ou de faire des jointures sur une valeur fréquemment. Une vraie colonne indexée battra à chaque fois un accès via un chemin JSON.
• Vous auriez logiquement utilisé une clé étrangère. Le JSON n'offre aucune intégrité relationnelle.

Le bon équilibre, c'est de mélanger les deux : des colonnes scalaires pour les champs qui pilotent vos requêtes et vos contraintes, et une colonne JSON à côté pour la longue traîne des données variables.

La suite : la recherche plein texte

Le JSON vous donne de la souplesse côté stockage. La page suivante s'attaque à FTS5, le moteur de recherche plein texte de SQLite, qui propose une vraie recherche textuelle avec classement et mise en évidence — bien au-delà de ce que permet LIKE.