Convertire dati JSON in righe e colonne con la funzione OPENJSON (SQL Server)Convert JSON Data to Rows and Columns with OPENJSON (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

La funzione dei set di righe OPENJSON consente di convertire testo JSON in un set di righe e colonne.The OPENJSON rowset function converts JSON text into a set of rows and columns. Dopo aver trasformato una raccolta JSON in un set di righe con OPENJSON, è possibile eseguire qualsiasi query SQL sui dati restituiti o inserirli in una tabella di SQL Server.After you transform a JSON collection into a rowset with OPENJSON, you can run any SQL query on the returned data or insert it into a SQL Server table.

La funzione OPENJSON accetta un singolo oggetto JSON o una raccolta di oggetti JSON e li trasforma in una o più righe.The OPENJSON function takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. Per impostazione predefinita, la funzione OPENJSON restituisce i dati seguenti:By default, the OPENJSON function returns the following data:

  • Da un oggetto JSON, la funzione restituisce tutte le coppie chiave:valore trovate al primo livello.From a JSON object, the function returns all the key:value pairs that it finds at the first level.
  • Da una matrice JSON, la funzione restituisce tutti gli elementi della matrice con i rispettivi indici.From a JSON array, the function returns all the elements of the array with their indexes.

È possibile aggiungere una clausola WITH facoltativa per specificare uno schema che definisce in modo esplicito la struttura dell'output.You can add an optional WITH clause to provide a schema that explicitly defines the structure of the output.

Opzione 1 - OPENJSON con l'output predefinitoOption 1 - OPENJSON with the default output

Quando si usa la funzione OPENJSON senza specificare uno schema esplicito per i risultati, ovvero senza una clausola WITH dopo OPENJSON, la funzione restituisce una tabella con le tre colonne seguenti:When you use the OPENJSON function without providing an explicit schema for the results - that is, without a WITH clause after OPENJSON - the function returns a table with the following three columns:

  1. Il nome della proprietà nell'oggetto di input o l'indice dell'elemento nella matrice di input.The name of the property in the input object (or the index of the element in the input array).
  2. Il valore della proprietà o l'elemento della matrice.The value of the property or the array element.
  3. Il tipo, ad esempio stringa, numero, valore booleano, matrice o oggetto.The type (for example, string, number, boolean, array, or object).

OPENJSON restituisce ogni proprietà dell'oggetto JSON o ogni elemento della matrice come riga separata.OPENJSON returns each property of the JSON object, or each element of the array, as a separate row.

L'esempio seguente usa la funzione OPENJSON con lo schema predefinito, ovvero senza la clausola WITH facoltativa, e restituisce una riga per ogni proprietà dell'oggetto JSON.Here's a quick example that uses OPENJSON with the default schema - that is, without the optional WITH clause - and returns one row for each property of the JSON object.

EsempioExample

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

RisultatiResults

Keykey Valorevalue tipotype
namename JohnJohn 11
surnamesurname DoeDoe 11
ageage 4545 22
skillsskills ["SQL","C#","MVC"]["SQL","C#","MVC"] 44

Altre informazioni su OPENJSON con lo schema predefinitoMore info about OPENJSON with the default schema

Per altre info e altri esempi, vedere Use OPENJSON with the Default Schema (SQL Server) (Usare OPENJSON con lo schema predefinito (SQL Server)).For more info and examples, see Use OPENJSON with the Default Schema (SQL Server).

Per la sintassi e l'utilizzo, vedere OPENJSON ( Transact-SQL ).For syntax and usage, see OPENJSON (Transact-SQL).

Opzione 2 - Output di OPENJSON con una struttura esplicitaOption 2 - OPENJSON output with an explicit structure

Quando si specifica uno schema per i risultati tramite la clausola WITH della funzione OPENJSON, la funzione restituisce una tabella che include solo le colonne definite nella clausola WITH.When you specify a schema for the results by using the WITH clause of the OPENJSON function, the function returns a table with only the columns that you define in the WITH clause. Nella clausola WITH facoltativa specificare un set di colonne di output, i relativi tipi e i percorsi delle proprietà di origine JSON per ogni valore di output.In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON esegue l'iterazione della matrice di oggetti JSON, legge il valore nel percorso specificato per ogni colonna e converte il valore nel tipo specificato.OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.

L'esempio seguente usa OPENJSON con uno schema per l'output specificato in modo esplicito nella clausola WITH.Here's a quick example that uses OPENJSON with a schema for the output that you explicitly specify in the WITH clause.

EsempioExample

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
       {  
         "Order": {  
           "Number":"SO43659",  
           "Date":"2011-05-31T00:00:00"  
         },  
         "AccountNumber":"AW29825",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":1  
         }  
       },  
       {  
         "Order": {  
           "Number":"SO43661",  
           "Date":"2011-06-01T00:00:00"  
         },  
         "AccountNumber":"AW73565",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":3  
         }  
      }  
 ]'  

SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              Number   varchar(200) '$.Order.Number' ,  
              Date     datetime     '$.Order.Date',  
              Customer varchar(200) '$.AccountNumber',  
              Quantity int          '$.Item.Quantity'  
 ) 

RisultatiResults

NumberNumber DataDate CustomerCustomer QuantityQuantity
SO43659SO43659 2011-05-31T00:00:002011-05-31T00:00:00 AW29825AW29825 11
SO43661SO43661 2011-06-01T00:00:002011-06-01T00:00:00 AW73565AW73565 33

Questa funzione restituisce e formatta gli elementi di una matrice JSON.This function returns and formats the elements of a JSON array.

  • Per ogni elemento nella matrice JSON la funzione OPENJSON genera una nuova riga nella tabella di output.For each element in the JSON array, OPENJSON generates a new row in the output table. I due elementi nella matrice JSON vengono convertiti in due righe nella tabella restituita.The two elements in the JSON array are converted into two rows in the returned table.

  • Per ogni colonna, specificata tramite la sintassi colName type json_path, OPENJSON converte il valore trovato in ogni elemento della matrice nel percorso specificato del tipo specificato.For each column, specified by using the colName type json_path syntax, OPENJSON converts the value found in each array element on the specified path to the specified type. In questo esempio i valori per la colonna Date sono ricavati da ogni elemento nel percorso $.Order.Date e convertiti in valori datetime.In this example, values for the Date column are taken from each element on the path $.Order.Date and converted to datetime values.

Altre informazioni su OPENJSON con uno schema esplicitoMore info about OPENJSON with an explicit schema

Per altre info e altri esempi, vedere Usare OPENJSON con uno schema esplicito (SQL Server).For more info and examples, see Use OPENJSON with an Explicit Schema (SQL Server).

Per la sintassi e l'utilizzo, vedere OPENJSON ( Transact-SQL ).For syntax and usage, see OPENJSON (Transact-SQL).

OPENJSON richiede il livello di compatibilità 130OPENJSON requires Compatibility Level 130

La funzione OPENJSON è disponibile per il livello di compatibilità 130.The OPENJSON function is available only under compatibility level 130. Se il livello di compatibilità del database è inferiore a 130, SQL Server non riuscirà a trovare e a eseguire la funzione OPENJSON.If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. Altre funzioni JSON predefinite sono disponibili in tutti i livelli di compatibilità.Other built-in JSON functions are available at all compatibility levels.

È possibile controllare il livello di compatibilità nella vista sys.databases o nelle proprietà del database.You can check compatibility level in the sys.databases view or in database properties.

È possibile modificare il livello di compatibilità del database tramite il comando seguente:You can change the compatibility level of a database by using the following command:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

Acquisire familiarità con il supporto JSON integrato in SQL ServerLearn more about the built-in JSON support in SQL Server

Per un numero elevato di soluzioni specifiche, casi di utilizzo e indicazioni, vedere il post di blog sul supporto JSON predefinito in SQL Server e Database SQL di Azure per Microsoft Program Manager Jovan Popovic.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.

Vedere ancheSee Also

OPENJSON ( Transact-SQL )OPENJSON (Transact-SQL)