Quando (e come) conservare JSON in SQL

I dati semi-strutturati — payload di API, attributi sparsi, documenti con schema variabile — trovano posto in SQL senza normalizzare tutto in tabelle. Storicamente il JSON si conserva in una colonna NVARCHAR(MAX), spesso protetta da un CHECK constraint con ISJSON() per rifiutare testo non valido. I motori più recenti (SQL Server 2025, Azure SQL Database, SQL database in Fabric) offrono un data type nativo json che memorizza il documento in forma binaria ottimizzata: valida automaticamente il contenuto e rende più efficienti lettura e aggiornamento parziale.

Il criterio d’esame è di merito, non di sintassi: usa JSON per ciò che è davvero eterogeneo o opzionale. Gli attributi che filtri, ordini o metti in join di frequente vanno modellati come colonne relazionali. JSON non sostituisce la normalizzazione: è un complemento per la parte flessibile del modello.

Leggere JSON: JSON_VALUE, JSON_QUERY e OPENJSON

JSON_VALUE estrae un valore scalare (stringa, numero, booleano) da un path e restituisce per default NVARCHAR(4000): se ti serve un tipo diverso applichi un CAST/CONVERT. Punta un scalar; su un oggetto o un array restituisce NULL (in path mode lax) o solleva errore (in strict).

JSON_QUERY fa il contrario: estrae un frammento (oggetto o array), non uno scalare. Confondere i due è un classico errore.

OPENJSON è la table-valued function che “sfoglia” il JSON in righe. Senza clausola WITH restituisce le colonne generiche key, value, type; con la clausola WITH definisci uno schema tipizzato che mappa ogni path a una colonna con il suo data type. È lo strumento giusto per trasformare un array JSON in un set di righe da mettere in join con dati relazionali, o per importare payload in bulk. Ricorda il path mode: lax (default) ignora i path mancanti, strict fa fallire la query se il path non esiste.

Scrivere JSON: FOR JSON

FOR JSON formatta il risultato di una query come testo JSON, utile per costruire response lato server. FOR JSON AUTO deriva la struttura (e il nesting) dalla forma dei join, comodo ma poco controllabile. FOR JSON PATH ti dà il controllo esplicito: gli alias di colonna con notazione a punti (cliente.nome) definiscono la gerarchia. Opzioni chiave da riconoscere: ROOT('...') avvolge tutto in un oggetto radice, INCLUDE_NULL_VALUES forza l’emissione dei campi NULL (per default vengono omessi), WITHOUT_ARRAY_WRAPPER produce un singolo oggetto anziché un array — indispensabile quando la query restituisce una sola riga.

Colonne calcolate e indici su path JSON

Filtrare con WHERE JSON_VALUE(doc, '$.stato') = 'attivo' funziona ma comporta una scansione: l’espressione non è sargable. La soluzione consolidata è creare una computed column che incapsula JSON_VALUE su quel path e costruirci sopra un indice nonclustered; da lì le query sul valore diventano indicizzate. La colonna calcolata può essere PERSISTED (materializzata su disco, utile anche come target di constraint) o non persistita: per essere indicizzabile deve comunque essere deterministica e precisa. Sui motori con data type json nativo esistono anche indici JSON nativi che coprono più path senza una colonna calcolata per ciascuno; valuta la disponibilità sulla piattaforma target prima di sceglierli.

Trappole tipiche d’esame

  • Estrarre un array o un sotto-oggetto da un documento → JSON_QUERY, non JSON_VALUE: JSON_VALUE è solo per scalari e restituisce NULL su oggetti/array; JSON_QUERY restituisce il frammento.
  • Trasformare un array JSON in righe da mettere in join → OPENJSON con clausola WITH: produce colonne tipizzate; JSON_VALUE estrae un solo valore, non un set.
  • Filtro su path JSON che deve usare un indice → computed column + nonclustered index: un WHERE diretto su JSON_VALUE non è sargable e scansiona la tabella.
  • Output di una sola riga come oggetto singolo → FOR JSON con WITHOUT_ARRAY_WRAPPER: senza questa opzione FOR JSON avvolge sempre in un array [...].
  • Includere nel JSON anche i campi valorizzati a NULL → INCLUDE_NULL_VALUES: per default FOR JSON li omette, alterando la forma attesa dal consumer.
  • Garantire che una colonna NVARCHAR contenga solo JSON valido → CHECK con ISJSON(): il data type NVARCHAR non valida nulla da solo; solo il type json nativo valida in automatico.