JSON in SQLite: json_extract, json_set e json_each

SQLite non ha un tipo JSON, e va bene così

SQLite non ha un tipo di colonna dedicato al JSON. Il JSON va in una normale colonna TEXT, e un insieme di funzioni integrate, chiamate nel complesso estensione JSON1, sa come analizzarlo, interrogarlo e modificarlo. JSON1 è incluso in ogni build moderna di SQLite, quindi non c'è nulla da installare.

Il modello mentale: salva il documento come testo, usa le funzioni per guardarci dentro.

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;

Due righe, ognuna con un documento JSON in una semplice colonna di testo. Ora servono dei modi per entrare in quei documenti.

Estrarre campi con json_extract e ->>

json_extract(column, path) estrae un valore da un documento JSON. Il percorso inizia con $ (la radice) e usa .field per le chiavi degli oggetti e [i] per gli indici degli array.

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;

Scrivere json_extract(data, '$.name') ovunque stanca presto, quindi SQLite ti dà due operatori:

• -> restituisce un valore codificato in JSON (le stringhe tornano con le virgolette).
• ->> restituisce un valore SQL (testo o numero, senza virgolette).

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 torna come "Ada" (ancora JSON), name_text come Ada. Usa ->> quando vuoi un valore da confrontare o mostrare. Usa -> quando passerai il risultato a un'altra funzione JSON.

Filtrare sui campi JSON

Una volta che sai estrarre, puoi filtrare. L'espressione va nella clausola WHERE come qualsiasi altra:

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;

Funziona, ma su una tabella di dimensioni serie è lento: ogni riga deve essere analizzata per valutare la condizione. Lo sistemeremo tra poco con un indice.

Costruire JSON: json_object e json_array

Nella direzione opposta, puoi costruire JSON dentro una query:

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, ...) costruisce un oggetto. json_array(v1, v2, ...) costruisce un array. Sono comode per assemblare le risposte di un'API direttamente in SQL, e si annidano senza problemi:

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

Aggiornare il JSON: json_set, json_insert, json_replace

Tre funzioni strettamente imparentate modificano un documento JSON e restituiscono la nuova versione:

• json_set(doc, path, value): imposta il percorso, creandolo se manca e sovrascrivendolo se esiste.
• json_insert(doc, path, value): inserisce solo se il percorso non esiste già.
• json_replace(doc, path, value): aggiorna solo se il percorso esiste già.

Le funzioni non modificano il documento sul posto: ne restituiscono uno nuovo, che di solito riscrivi con 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;

Nota che json_set accetta più coppie percorso/valore in una sola chiamata. Per rimuovere una chiave, usa json_remove(doc, path).

Espandere gli array con json_each

json_each è una funzione che restituisce una tabella: prende un array (o un oggetto) JSON e restituisce una riga per ogni elemento. Così "trova gli utenti con il tag admin", scomodo in SQL puro, diventa una normale 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';

Ogni riga di users viene unita agli elementi del suo array tags. json_each espone colonne utili tra cui key, value, type e fullkey. La sua sorella json_tree percorre ricorsivamente l'intero documento, compresi tutti i nodi annidati: comoda per cercare in documenti di cui non conosci la struttura.

Indicizzare i campi JSON

La query WHERE data ->> '$.active' = 1 vista sopra funziona, ma SQLite deve analizzare ogni riga per valutare la condizione. Per i campi che interroghi spesso, crea un indice su espressione:

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'indice deve usare esattamente la stessa espressione della query. Se nell'indice usi json_extract(data, '$.email') e nella query data ->> '$.email', non corrispondono e l'indice resta inutilizzato: scegli una forma e mantienila.

Per i campi che interroghi di continuo, una colonna generata si legge meglio:

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

Per chi scrive le query email sembra una colonna normale, ma resta automaticamente sincronizzata con il JSON.

Validare il JSON

json_valid(text) restituisce 1 se il testo è JSON valido, 0 altrimenti. Abbinala a un vincolo CHECK per rifiutare i dati sbagliati già in scrittura:

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

Il primo inserimento riesce; il secondo fallisce con un errore di vincolo. Senza quel controllo, il JSON malformato resta tranquillo nella tabella finché, mesi dopo, qualche chiamata a json_extract va in errore.

JSON e JSONB

Da SQLite 3.45 esiste una rappresentazione binaria chiamata JSONB: gli stessi dati, già analizzati e salvati in una forma binaria compatta, così le funzioni non devono rianalizzarli a ogni chiamata. La famiglia di funzioni jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) restituisce JSONB invece di testo, e le colonne JSONB si interrogano con gli stessi operatori.

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;

Usa il JSON normale (testo) quando vuoi documenti leggibili nei dump e facili da ispezionare. Passa a JSONB quando una tabella è grande, interrogata spesso e il costo dell'analisi emerge davvero nel profiling. Non cambiare per principio: la leggibilità del JSON normale vale molto durante il debug.

Quando il JSON è la scelta giusta

Le colonne JSON danno il meglio quando:

• La struttura cambia da una riga all'altra (pensa ai payload degli eventi, ai log di audit, ai webhook delle integrazioni).
• Stai salvando in cache la risposta di un'API esterna e vuoi conservarla intatta.
• Un campo viene interrogato di rado e non si filtra quasi mai su di esso.

Sono una cattiva scelta quando:

• Usi il JSON per evitare di progettare uno schema. Se ogni riga ha gli stessi campi, quelli sono colonne.
• Devi filtrare o fare join su un valore di frequente. Una colonna vera con un indice batterà ogni volta una ricerca su un percorso JSON.
• Ti servirebbero le chiavi esterne. Il JSON non ha integrità relazionale.

La soluzione ideale è combinare le due cose: colonne scalari per i campi che guidano query e vincoli, e accanto una colonna JSON per la lunga coda di dati variabili.

Prossimo passo: la ricerca full-text

Il JSON ti dà flessibilità lato storage. La prossima pagina tratta FTS5, il motore di ricerca full-text di SQLite, che ti offre una vera ricerca testuale con ranking ed evidenziazione, molto oltre quello che può fare LIKE.