Risolvere i problemi comuni di JSON in SQL ServerSolve common issues with JSON in SQL Server

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In questo articolo sono contenute le risposte ad alcune domande comuni sul supporto JSON integrato in SQL Server.Find answers here to some common questions about the built-in JSON support in SQL Server.

Output di FOR JSON e JSONFOR JSON and JSON output

FOR JSON PATH o FOR JSON AUTO?FOR JSON PATH or FOR JSON AUTO?

Domanda.Question. Si desidera creare un risultato di testo JSON da una semplice query SQL su una singola tabella.I want to create a JSON text result from a simple SQL query on a single table. FOR JSON PATH e FOR JSON AUTO producono lo stesso output.FOR JSON PATH and FOR JSON AUTO produce the same output. Quali di queste due opzioni deve essere usata?Which of these two options should I use?

Risposta.Answer. Usare FOR JSON PATH.Use FOR JSON PATH. Anche se non esiste alcuna differenza nell'output JSON, la modalità AUTO dispone di una logica aggiuntiva che controlla se le colonne devono essere nidificate.Although there is no difference in the JSON output, AUTO mode applies some additional logic that checks whether columns should be nested. Considerare PATH come opzione predefinita.Consider PATH the default option.

Creare una struttura JSON nidificataCreate a nested JSON structure

Domanda.Question. Si desidera produrre una struttura JSON complessa con numerose matrici sullo stesso livello.I want to produce complex JSON with several arrays on the same level. FOR JSON PATH è in grado di creare oggetti nidificati usando percorsi e FOR JSON AUTO crea livelli di nidificazione aggiuntivi per ciascuna tabella.FOR JSON PATH can create nested objects using paths, and FOR JSON AUTO creates additional nesting level for each table. Nessuna di queste due opzioni consente di generare l'output desiderato.Neither one of these two options lets me generate the output I want. Come è possibile creare un formato JSON personalizzato che le opzioni esistenti non supportano direttamente?How can I create a custom JSON format that the existing options don't directly support?

Risposta.Answer. È possibile creare qualsiasi struttura dei dati aggiungendo query FOR JSON come espressioni di colonna che restituiscono testo JSON.You can create any data structure by adding FOR JSON queries as column expressions that return JSON text. È anche possibile creare manualmente JSON tramite la funzione JSON_QUERY.You can also create JSON manually by using the JSON_QUERY function. Queste tecniche vengono illustrate nell'esempio seguente.The the following example demonstrates these techniques.

SELECT col1, col2, col3,  
     (SELECT col11, col12, col13 FROM t11 WHERE t11.FK = t1.PK FOR JSON PATH) as t11,  
     (SELECT col21, col22, col23 FROM t21 WHERE t21.FK = t1.PK FOR JSON PATH) as t21,  
     (SELECT col31, col32, col33 FROM t31 WHERE t31.FK = t1.PK FOR JSON PATH) as t31,  
     JSON_QUERY('{"'+col4'":"'+col5+'"}' as t41  
FROM t1  
FOR JSON PATH  

Ogni risultato di una query FOR JSON o della funzione JSON_QUERY nelle espressioni di colonna è formattato come un oggetto secondario JSON nidificato a parte e incluso nel risultato principale.Every result of a FOR JSON query or the JSON_QUERY function in the column expressions is formatted as a separate nested JSON sub-object and included in the main result.

Evitare JSON con doppi caratteri di escape nell'output FOR JSONPrevent double-escaped JSON in FOR JSON output

Domanda.Question. Il testo JSON è archiviato in una colonna di tabella.I have JSON text stored in a table column. Si desidera includerlo nell'output di FOR JSON.I want to include it in the output of FOR JSON. Tuttavia, FOR JSON usa caratteri di escape per tutti i caratteri nel file JSON, quindi si ottiene una stringa JSON anziché un oggetto nidificato, come illustrato nell'esempio seguente.But FOR JSON escapes all characters in the JSON, so I’m getting a JSON string instead of a nested object, as shown in the following example.

SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH  

Questa query produce l'output riportato di seguito.This query produces the following output.

[{"myText":"Text", "myJson":"{\"day\":23}"}]  

Come è possibile evitare questo comportamento?How can I prevent this behavior? Si desidera che {"day":23} venga restituito come oggetto JSON e non come testo con caratteri di escape.I want {"day":23} to be returned as a JSON object and not as escaped text.

Risposta.Answer. L'oggetto JSON archiviato in una colonna di testo o in un valore letterale viene trattato come qualsiasi testo.JSON stored in a text column or a literal is treated like any text. Ovvero, è racchiuso tra virgolette doppie e caratteri di escape.That is, it's surrounded with double quotes and escaped. Se si desidera restituire un oggetto JSON senza caratteri di escape, passare la colonna JSON come argomento alla funzione JSON_QUERY, come illustrato nell'esempio seguente.If you want to return an unescaped JSON object, pass the JSON column as an argument to the JSON_QUERY function, as shown in the following example.

SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH  

JSON_QUERY, senza il secondo parametro facoltativo, restituisce il primo argomento come risultato.JSON_QUERY without its optional second parameter returns only the first argument as a result. Poiché JSON_QUERY restituisce sempre un oggetto JSON valido, FOR JSON sa che questo risultato non deve essere preceduto da caratteri di escape.Since JSON_QUERY always returns valid JSON, FOR JSON knows that this result does not have to be escaped.

JSON generato con la clausola WITHOUT_ARRAY_WRAPPER viene preceduto da caratteri di escape nell'output FOR JSONJSON generated with the WITHOUT_ARRAY_WRAPPER clause is escaped in FOR JSON output

Domanda.Question. Si tenta di formattare un'espressione di colonna con FOR JSON e l'opzione WITHOUT_ARRAY_WRAPPER.I’m trying to format a column expression by using FOR JSON and the WITHOUT_ARRAY_WRAPPER option.

SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH   

Sembra che il testo restituito dalla query FOR JSON sia preceduto da caratteri di escape come testo normale.It seems that the text returned by the FOR JSON query is escaped as plain text. Ciò si verifica solo se la clausola WITHOUT_ARRAY_WRAPPER è specificata.This happens only if WITHOUT_ARRAY_WRAPPER is specified. Perché non viene trattato come un oggetto JSON e incluso senza caratteri di escape nel risultato?Why isn't it treated as a JSON object and included unescaped in the result?

Risposta.Answer. Se si specifica l'opzione WITHOUT_ARRAY_WRAPPER nella query FOR JSON interna, il testo JSON risultante non è necessariamente JSON valido.If you specify the WITHOUT_ARRAY_WRAPPER option in the inner FOR JSON, the resulting JSON text is not necessarily valid JSON. Pertanto la query FOR JSON esterna presuppone che si tratti di testo normale e usa caratteri di escape per la stringa.Therefore the outer FOR JSON assumes that this is plain text and escapes the string. Se si è certi che l'output JSON sia valido, eseguirne il wrapping con la funzione JSON_QUERY per convertirlo in oggetto JSON formattato correttamente, come illustrato nell'esempio seguente.If you are sure that the JSON output is valid, wrap it with the JSON_QUERY function to promote it to properly formatted JSON, as shown in the following example.

SELECT 'Text' as myText,  
      JSON_QUERY((SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as myJson  
FOR JSON PATH    

Input di OPENJSON e JSONOPENJSON and JSON input

Restituire l'oggetto secondario JSON nidificato dal testo JSON con OPENJSONReturn a nested JSON sub-object from JSON text with OPENJSON

Domanda.Question. Non è possibile aprire una matrice di oggetti JSON complessi che contiene sia valori scalari sia oggetti, e matrici usando OPENJSON con uno schema esplicito.I can't open an array of complex JSON objects that contains both scalar values, objects, and arrays using OPENJSON with an explicit schema. Quando si aggiunge un riferimento a una chiave nella clausola WITH, vengono restituiti solo i valori scalari.When I reference a key in the WITH clause, only scalar values are returned. Oggetti e matrici vengono restituiti come valori null.Objects and arrays are returned as null values. Come è possibile estrarre oggetti o matrici come oggetti JSON?How can I extract objects or arrays as JSON objects?

Risposta.Answer. Se si desidera restituire un oggetto o una matrice come colonna, usare l'opzione AS JSON nella definizione di colonna, come illustrato nell'esempio seguente.If you want to return an object or an array as a column, use the AS JSON option in the column definition, as shown in the following example.

SELECT scalar1, scalar2, obj1, obj2, arr1  
FROM OPENJSON(@json)  
    WITH ( scalar1 int,  
        scalar2 datetime2,  
        obj1 NVARCHAR(MAX) AS JSON,  
        obj2 NVARCHAR(MAX) AS JSON,  
        arr1 NVARCHAR(MAX) AS JSON)  

Restituire il valore di testo lungo con OPENJSON anziché JSON_VALUEReturn long text value with OPENJSON instead of JSON_VALUE

Domanda.Question. La chiave di descrizione nel testo JSON contiene testo lungo.I have description key in JSON text that contains long text. JSON_VALUE(@json, '$.description') restituisce NULL anziché un valore.JSON_VALUE(@json, '$.description') returns NULL instead of a value.

Risposta.Answer. JSON_VALUE è progettato per restituire valori scalari piccoli.JSON_VALUE is designed to return small scalar values. In genere la funzione restituisce NULL anziché un errore di overflow.Generally the function returns NULL instead of an overflow error. Se si desidera restituire valori più lunghi, usare OPENJSON, che supporta valori NVARCHAR (MAX), come illustrato nell'esempio seguente.If you want to return longer values, use OPENJSON, which supports NVARCHAR(MAX) values, as shown in the following example.

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')  

Gestire chiavi duplicate con OPENJSON anziché con JSON_VALUEHandle duplicate keys with OPENJSON instead of JSON_VALUE

Domanda.Question. Il testo JSON contiene chiavi duplicate.I have duplicate keys in the JSON text. JSON_VALUE restituisce solo la prima chiave trovata nel percorso.JSON_VALUE returns only the first key found on the path. Come è possibile visualizzare tutte le chiavi con lo stesso nome?How can I return all keys that have the same name?

Risposta.Answer. Le funzioni scalari JSON integrate restituiscono solo la prima occorrenza dell'oggetto di riferimento.The built-in JSON scalar functions return only the first occurrence of the referenced object. Se è necessaria più di una chiave, usare la funzione con valori di tabella OPENJSON, come illustrato nell'esempio seguente.If you need more than one key, use the OPENJSON table-valued function, as shown in the following example.

SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'  

OPENJSON richiede il livello di compatibilità 130OPENJSON requires compatibility level 130

Domanda.Question. È in corso un tentativo di esecuzione di OPENJSON in SQL Server 2016 e viene visualizzato l'errore riportato di seguito.I’m trying to run OPENJSON in SQL Server 2016 and I’m getting the following error.

Msg 208, Level 16, State 1 ‘Invalid object name OPENJSON’

Risposta.Answer. La funzione OPENJSON è disponibile solo nel livello di compatibilità 130.The OPENJSON function is available only under compatibility level 130. Se il livello di compatibilità del database è inferiore a 130, la funzione OPENJSON è nascosta.If your DB compatibility level is lower than 130, OPENJSON is hidden. Altre funzioni JSON sono disponibili in tutti i livelli di compatibilità.Other JSON functions are available at all compatibility levels.

Altre domandeOther questions

Aggiungere il riferimento alle chiavi che contengono caratteri non alfanumerici nel testo JSONReference keys that contain non-alphanumeric characters in JSON text

Domanda.Question. Le chiavi del testo JSON contengono caratteri non alfanumerici.I have non-alphanumeric characters in keys in my JSON text. Come è possibile aggiungere un riferimento a queste proprietà?How can I reference these properties?

Risposta.Answer. È necessario racchiuderle tra virgolette nei percorsi JSON.You have to surround them with quotes in JSON paths. Ad esempio, JSON_VALUE(@json, '$."$info"."First Name".value').For example, JSON_VALUE(@json, '$."$info"."First Name".value').

Altre informazioni sul supporto JSON integrato in SQL ServerLearn more about the built-in JSON support in SQL Server

Per soluzioni specifiche, casi d'uso e indicazioni, vedere i post del blog sul supporto JSON integrato in SQL Server e nel database SQL di Azure redatti da Jovan Popovic, Microsoft Program Manager.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.