Formattare i risultati delle query in formato JSON con FOR JSON

Si applica a: SQL Server 2016 (13.x) e versioni successive Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (solo pool SQL senza server)Endpoint di analisi SQL in Microsoft FabricMagazzino in Microsoft FabricDatabase SQL in Microsoft Fabric

È 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.

Note

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.

In Fabric Data Warehouse FOR JSON deve essere l'ultimo operatore nella query e pertanto non è consentito all'interno di sottoquery.

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 informazioni ed esempi più dettagliati, vedere Formattare l'Output JSON Annidato con la Modalità PATH.

Per la sintassi e l'utilizzo, vedere Clausola SELECT - 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 informazioni più dettagliate ed esempi, vedi Formattare automaticamente l'output JSON con la modalità AUTO.

Per la sintassi e l'utilizzo, vedere Clausola SELECT - 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.

• 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 ulteriori informazioni, vedere Includere valori nulli 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 contenente una sola riga. 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#.

       Screenshot dell'output FOR JSON in SQL Server Management Studio.

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 How FOR JSON escapes special characters and control characters (Come FOR JSON esegue l'escape di caratteri speciali e caratteri di controllo).

Example

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

Risultati delle query

A	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"
}]

Contenuti correlati

• Come FOR JSON converte i tipi di dati di SQL Server in tipi di dati JSON
• Come FOR JSON esegue l'escape dei caratteri speciali e di controllo
• Video: JSON come bridge tra NoSQL e mondi relazionali
• Clausola SELECT - FOR (Transact-SQL)
• Usare l'output FOR JSON nel motore di database SQL e nelle app client