Formattare i risultati delle query in formato JSON con FOR JSON

Si applica a: SQL Server 2016 (13.x) e versioni successive Database SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics (solo pool SQL serverless)

È possibile formattare i risultati delle query come JSON o esportare i dati da SQL Server in formato JSON aggiungendo la clausola FOR JSON a un'istruzione SELECT. Usare la clausola FOR JSON per semplificare le applicazioni client delegando la formattazione dell'output JSON dell'app a SQL Server.

Nota

Azure Data Studio è l'editor di query consigliato per le query JSON perché formatta automaticamente i risultati JSON, come illustrato in questo articolo. SQL Server Management Studio visualizza una stringa non formattata.

Formattare i risultati delle query

Quando si usa la clausola FOR JSON, è possibile specificare la struttura dell'output JSON in modo esplicito o determinarla automaticamente in base alla struttura dell'istruzione SELECT.

• Per mantenere il controllo completo sul formato dell'output JSON, usare FOR JSON PATH. È possibile creare oggetti wrapper e annidare proprietà complesse.

• Per formattare automaticamente l'output JSON in base alla struttura dell'istruzione SELECT, usare FOR JSON AUTO.

Di seguito è riportato un esempio di istruzione SELECT con la clausola FOR JSON e il relativo output.

FOR JSON PATH

Controllo dell'output con FOR JSON PATH

In modalità PATH è possibile utilizzare la sintassi a punti, ad esempio Item.Price, per formattare l'output annidato.

Ecco una query di esempio che usa la modalità PATH con la clausola FOR JSON. L'esempio seguente usa anche l'opzione ROOT per specificare un elemento radice denominato.

Altre informazioni su FOR JSON PATH

Per altre informazioni ed esempi, vedere Formattare l'output JSON annidato con la modalità PATH (SQL Server).

Per la sintassi e l'uso, vedere Clausola FOR (Transact-SQL).

FOR JSON AUTO

L'istruzione SELECT controlla l'output con FOR JSON AUTO

In modalità AUTO, la struttura dell'istruzione SELECT determina il formato dell'output JSON.

Per impostazione predefinita, i valori NULL non vengono inclusi nell'output. Per modificare questo comportamento è possibile utilizzare INCLUDE_NULL_VALUES.

Ecco una query di esempio che usa la modalità AUTO con la clausola FOR JSON.

SELECT name, surname
FROM emp
FOR JSON AUTO;

Ed ecco il codice JSON restituito.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

Esempio con JOIN e NULL

Il seguente esempio di SELECT...FOR JSON AUTO include una visualizzazione dell'aspetto dei risultati JSON quando esiste una relazione 1 a molti tra i dati da tabelle unite.

Viene illustrata anche l'assenza del valore Null nel codice JSON restituito. Tuttavia, è possibile eseguire l'override di questo comportamento predefinito usando la parola chiave INCLUDE_NULL_VALUES nella clausola FOR.

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

Di seguito è riportato l'output JSON dell'operazione SELECT precedente.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Altre informazioni su FOR JSON AUTO

Per altre informazioni ed esempi, vedere Formattare automaticamente l'output JSON con la modalità AUTO (SQL Server).

Per la sintassi e l'uso, vedere Clausola FOR (Transact-SQL).

Controllare altre opzioni di output JSON

Controllare l'output della clausola FOR JSON utilizzando le seguenti opzioni aggiuntive.

• ROOT

  Per aggiungere un unico elemento di primo livello all'output JSON, specificare l'opzione ROOT. Se non si specifica questa opzione, l'output JSON non ha alcun elemento radice. Per altre informazioni, vedere Aggiungere un nodo radice all'output JSON con l'opzione ROOT (SQL Server).

• INCLUDE_NULL_VALUES

  Per includere valori null nell'output JSON, specificare l'opzione INCLUDE_NULL_VALUES. Se non si specifica questa opzione, l'output non include le proprietà JSON per i valori NULL presenti nei risultati della query. Per altre informazioni, vedere Includere valori in JSON - Opzione INCLUDE_NULL_VALUES.

• WITHOUT_ARRAY_WRAPPER

  Per rimuovere le parentesi quadre che racchiudono l'output JSON della clausola FOR JSON per impostazione predefinita, specificare l'opzione WITHOUT_ARRAY_WRAPPER. Usare questa opzione per generare un singolo oggetto JSON come output da un risultato a riga singola. Se non si specifica questa opzione, l'output JSON viene formattato come una matrice, ovvero è racchiuso tra parentesi quadre. Per altre informazioni, vedere Rimuovere le parentesi quadre da JSON - Opzione WITHOUT_ARRAY_WRAPPER.

Output della clausola FOR JSON

L'output della clausola FOR JSON ha le seguenti caratteristiche:

1. Il set di risultati contiene un'unica colonna.

   • Un set di risultati di piccole dimensioni può contenere un'unica riga.
   • Un set di risultati di grandi dimensioni suddivide la stringa JSON su più righe.

     • Per impostazione predefinita, SQL Server Management Studio (SSMS) concatena i risultati in una singola riga quando l'impostazione dell'output è Risultati in formato griglia. La barra di stato di SQL Server Management Studio visualizza il conteggio effettivo delle righe.

     • Altre applicazioni client possono richiedere codice per ricombinare i risultati eccessivamente lunghi in un'unica stringa JSON valida concatenando i contenuti di più righe. Per un esempio di questo codice in un'applicazione C#, vedere Usare l'output FOR JSON in un'app client C#.

       

2. I risultati vengono formattati sotto forma di matrice di oggetti JSON.

   • Il numero di elementi nella matrice JSON è uguale al numero di righe nei risultati dell'istruzione SELECT (prima che venga applicata la clausola FOR JSON).

   • Ogni riga nei risultati dell'istruzione SELECT (prima che venga applicata la clausola FOR JSON) diventa un oggetto JSON separato nella matrice.

   • Ogni colonna nei risultati dell'istruzione SELECT (prima che venga applicata la clausola FOR JSON) diventa una proprietà dell'oggetto JSON.

3. I nomi delle colonne e i valori corrispondenti vengono sottoposti a escape in base alla sintassi JSON. Per altre informazioni, vedere Sequenze di escape FOR JSON per i caratteri speciali e di controllo (SQL Server).

Esempio

Ecco un esempio che illustra come la clausola FOR JSON formatta l'output JSON.

Risultati query

Un	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

Output JSON

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]

Contenuto correlato

• Modalità di conversione di FOR JSON dei tipi di dati SQL Server in tipi di dati JSON (SQL Server)
• Modalità di uso di FOR JSON delle sequenze di escape per i caratteri speciali e di controllo (SQL Server)
• JSON as a bridge between NoSQL and relational worlds (JSON come ponte tra NoSQL e gli ambienti relazionali)
• Clausola SELECT - FOR (Transact-SQL)
• Usare l'output FOR JSON in SQL Server e nelle app client (SQL Server)