Introduzione alle funzionalità JSON nel database SQL di AzureGetting started with JSON features in Azure SQL Database

Il database SQL di Azure consente di analizzare ed eseguire query sui dati rappresentati in formato JavaScript Object Notation (JSON) e di esportare i dati relazionali come testo JSON.Azure SQL Database lets you parse and query data represented in JavaScript Object Notation (JSON) format, and export your relational data as JSON text.

JSON è un formato di dati diffuso usato per scambiare dati nelle moderne applicazioni Web e per dispositivi mobili.JSON is a popular data format used for exchanging data in modern web and mobile applications. JSON è usato anche per archiviare dati semistrutturati nei file di log o nei database NoSQL come Azure Cosmos DB.JSON is also used for storing semi-structured data in log files or in NoSQL databases like Azure Cosmos DB. Molti servizi Web REST restituiscono risultati formattati come testo JSON o accettano dati formattati come JSON.Many REST web services return results formatted as JSON text or accept data formatted as JSON. La maggior parte dei servizi di Azure come Ricerca di Azure, Archiviazione di Azure e Azure Cosmos DB presentano endpoint REST che restituiscono o usano JSON.Most Azure services such as Azure Search, Azure Storage, and Azure Cosmos DB have REST endpoints that return or consume JSON.

Il database SQL di Azure consente di lavorare facilmente con dati JSON e integrare il database con i servizi moderni.Azure SQL Database lets you work with JSON data easily and integrate your database with modern services.

OverviewOverview

Il database SQL di Azure fornisce le funzioni seguenti per lavorare con i dati JSON:Azure SQL Database provides the following functions for working with JSON data:

Funzioni di JSON

Se si dispone di testo JSON, è possibile estrarre dati da JSON o verificare che JSON sia formattato correttamente usando le funzioni predefinite JSON_VALUE, JSON_QUERY e ISJSON.If you have JSON text, you can extract data from JSON or verify that JSON is properly formatted by using the built-in functions JSON_VALUE, JSON_QUERY, and ISJSON. La funzione JSON_MODIFY consente di aggiornare il valore all'interno del testo JSON.The JSON_MODIFY function lets you update value inside JSON text. Per query e analisi più avanzate, la funzione OPENJSON può trasformare un array di oggetti JSON in un set di righe.For more advanced querying and analysis, OPENJSON function can transform an array of JSON objects into a set of rows. È possibile eseguire qualsiasi query SQL sul set di risultati restituito.Any SQL query can be executed on the returned result set. Infine, è disponibile una clausola FOR JSON che consente di formattare i dati archiviati nelle tabelle relazionali come testo JSON.Finally, there is a FOR JSON clause that lets you format data stored in your relational tables as JSON text.

Formattazione di dati relazionali in formato JSONFormatting relational data in JSON format

Se si dispone di un servizio Web che accetta dati dal livello di database e fornisce una risposta in formato JSON o framework JavaScript sul lato client o raccolte che accettano dati formattati come JSON, è possibile formattare il contenuto del database nel formato JSON direttamente in una query SQL.If you have a web service that takes data from the database layer and provides a response in JSON format, or client-side JavaScript frameworks or libraries that accept data formatted as JSON, you can format your database content as JSON directly in a SQL query. Non è più necessario scrivere il codice dell'applicazione che formatta i risultati dal database SQL di Azure come JSON o includere alcune librerie di serializzazione JSON per convertire i risultati della query in formato tabulare e quindi serializzare oggetti in formato JSON.You no longer have to write application code that formats results from Azure SQL Database as JSON, or include some JSON serialization library to convert tabular query results and then serialize objects to JSON format. È possibile usare la clausola FOR JSON per formattare i risultati della query SQL come JSON nel database SQL di Azure e usarla direttamente nell'applicazione.Instead, you can use the FOR JSON clause to format SQL query results as JSON in Azure SQL Database and use it directly in your application.

Nell'esempio seguente, le righe della tabella Sales.Customer vengono formattate come JSON usando la clausola FOR JSON:In the following example, rows from the Sales.Customer table are formatted as JSON by using the FOR JSON clause:

select CustomerName, PhoneNumber, FaxNumber
from Sales.Customers
FOR JSON PATH

La clausola FOR JSON PATH formatta i risultati della query come testo JSON.The FOR JSON PATH clause formats the results of the query as JSON text. I nomi di colonna vengono usati come chiavi, mentre i valori di cella vengono generati come valori JSON:Column names are used as keys, while the cell values are generated as JSON values:

[
{"CustomerName":"Eric Torres","PhoneNumber":"(307) 555-0100","FaxNumber":"(307) 555-0101"},
{"CustomerName":"Cosmina Vlad","PhoneNumber":"(505) 555-0100","FaxNumber":"(505) 555-0101"},
{"CustomerName":"Bala Dixit","PhoneNumber":"(209) 555-0100","FaxNumber":"(209) 555-0101"}
]

Il set di risultati viene formattato come array JSON in cui ogni riga viene formattata come un oggetto JSON separato.The result set is formatted as a JSON array where each row is formatted as a separate JSON object.

PATH indica che è possibile personalizzare il formato di output del risultato JSON usando la notazione del punto negli alias di colonna.PATH indicates that you can customize the output format of your JSON result by using dot notation in column aliases. La query seguente cambia il nome della chiave "CustomerName" nel formato JSON di output e inserisce i numeri di telefono e fax nell'oggetto secondario "Contatto":The following query changes the name of the "CustomerName" key in the output JSON format, and puts phone and fax numbers in the "Contact" sub-object:

select CustomerName as Name, PhoneNumber as [Contact.Phone], FaxNumber as [Contact.Fax]
from Sales.Customers
where CustomerID = 931
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

L'output di questa query è simile al seguente:The output of this query looks like this:

{
    "Name":"Nada Jovanovic",
    "Contact":{
           "Phone":"(215) 555-0100",
           "Fax":"(215) 555-0101"
    }
}

In questo esempio viene restituito un singolo oggetto JSON anziché un array, specificando l'opzione WITHOUT_ARRAY_WRAPPER.In this example we returned a single JSON object instead of an array by specifying the WITHOUT_ARRAY_WRAPPER option. È possibile usare questa opzione se è noto che si sta restituendo un oggetto singolo come risultato della query.You can use this option if you know that you are returning a single object as a result of query.

Il valore principale della clausola FOR JSON è che consente di restituire dati gerarchici complessi dal database formattati come array o oggetti JSON annidati.The main value of the FOR JSON clause is that it lets you return complex hierarchical data from your database formatted as nested JSON objects or arrays. Nell'esempio seguente viene illustrato come includere gli ordini che appartengono al cliente come array annidato di ordini:The following example shows how to include Orders that belong to the Customer as a nested array of Orders:

select CustomerName as Name, PhoneNumber as Phone, FaxNumber as Fax,
        Orders.OrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate
from Sales.Customers Customer
    join Sales.Orders Orders
        on Customer.CustomerID = Orders.CustomerID
where Customer.CustomerID = 931
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

Anziché inviare query separate per ottenere i dati dei clienti e quindi per recuperare un elenco di ordini correlati, è possibile ottenere tutti i dati necessari con una singola query, come illustrato nel seguente output di esempio:Instead of sending separate queries to get Customer data and then to fetch a list of related Orders, you can get all the necessary data with a single query, as shown in the following sample output:

{
  "Name":"Nada Jovanovic",
  "Phone":"(215) 555-0100",
  "Fax":"(215) 555-0101",
  "Orders":[
    {"OrderID":382,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":395,"OrderDate":"2013-01-07","ExpectedDeliveryDate":"2013-01-08"},
    {"OrderID":1657,"OrderDate":"2013-01-31","ExpectedDeliveryDate":"2013-02-01"}
]
}

Uso dei dati JSONWorking with JSON data

Se non si dispone di dati rigorosamente strutturati, se si dispone di oggetti secondari, array o dati gerarchici complessi, oppure se le strutture di dati si evolvono nel tempo, il formato JSON può essere utile per rappresentare una struttura di dati complessa.If you don’t have strictly structured data, if you have complex sub-objects, arrays, or hierarchical data, or if your data structures evolve over time, the JSON format can help you to represent any complex data structure.

JSON è un formato testuale che può essere usato come qualsiasi altro tipo di stringa nel database SQL di Azure.JSON is a textual format that can be used like any other string type in Azure SQL Database. È possibile inviare o archiviare i dati JSON come NVARCHAR standard:You can send or store JSON data as a standard NVARCHAR:

CREATE TABLE Products (
  Id int identity primary key,
  Title nvarchar(200),
  Data nvarchar(max)
)
go
CREATE PROCEDURE InsertProduct(@title nvarchar(200), @json nvarchar(max))
AS BEGIN
    insert into Products(Title, Data)
    values(@title, @json)
END

I dati JSON usati in questo esempio vengono rappresentati con il tipo NVARCHAR(MAX).The JSON data used in this example is represented by using the NVARCHAR(MAX) type. Il formato JSON può essere inserito in questa tabella o fornito come argomento della stored procedure usando la sintassi Transact-SQL standard, come illustrato nell'esempio seguente:JSON can be inserted into this table or provided as an argument of the stored procedure using standard Transact-SQL syntax as shown in the following example:

EXEC InsertProduct 'Toy car', '{"Price":50,"Color":"White","tags":["toy","children","games"]}'

Qualsiasi lingua o raccolta client-side compatibile con i dati della stringa nel database SQL di Azure è compatibile anche con i dati JSON.Any client-side language or library that works with string data in Azure SQL Database will also work with JSON data. I dati JSON possono essere archiviati in qualsiasi tabella che supporta il tipo NVARCHAR, ad esempio una tabella con ottimizzazione per la memoria o una tabella con controllo delle versioni del sistema.JSON can be stored in any table that supports the NVARCHAR type, such as a Memory-optimized table or a System-versioned table. Il formato JSON non introduce alcun vincolo nel codice lato client o nel livello di database.JSON does not introduce any constraint either in the client-side code or in the database layer.

Query sui dati JSONQuerying JSON data

Se si dispone di dati formattati come JSON archiviati in tabelle SQL di Azure, le funzioni JSON consentono di usare i dati in qualsiasi query SQL.If you have data formatted as JSON stored in Azure SQL tables, JSON functions let you use this data in any SQL query.

Le funzioni JSON disponibili nel database SQL di Azure consentono di trattare i dati formattati come JSON come qualsiasi altro tipo di dati SQL.JSON functions that are available in Azure SQL database let you treat data formatted as JSON as any other SQL data type. È facilmente possibile estrarre i valori dal testo JSON e usare dati JSON in qualsiasi query:You can easily extract values from the JSON text, and use JSON data in any query:

select Id, Title, JSON_VALUE(Data, '$.Color'), JSON_QUERY(Data, '$.tags')
from Products
where JSON_VALUE(Data, '$.Color') = 'White'

update Products
set Data = JSON_MODIFY(Data, '$.Price', 60)
where Id = 1

La funzione JSON_VALUE estrae un valore dal testo JSON archiviato nella colonna Data.The JSON_VALUE function extracts a value from JSON text stored in the Data column. Questa funzione usa un percorso in stile JavaScript per fare riferimento a un valore nel testo JSON da estrarre.This function uses a JavaScript-like path to reference a value in JSON text to extract. Il valore estratto può essere usato in qualsiasi parte di una query SQL.The extracted value can be used in any part of SQL query.

La funzione JSON_QUERY è simile a JSON_VALUE.The JSON_QUERY function is similar to JSON_VALUE. A differenza di JSON_VALUE, questa funzione estrae l'oggetto secondario complesso, ad esempio array o oggetti che vengono inseriti nel testo JSON.Unlike JSON_VALUE, this function extracts complex sub-object such as arrays or objects that are placed in JSON text.

La funzione JSON_MODIFY consente di specificare il percorso del valore nel testo JSON che deve essere aggiornato, nonché un nuovo valore che sovrascriverà quello precedente.The JSON_MODIFY function lets you specify the path of the value in the JSON text that should be updated, as well as a new value that will overwrite the old one. In questo modo è possibile aggiornare facilmente il testo JSON senza ripetere l'analisi dell'intera struttura.This way you can easily update JSON text without reparsing the entire structure.

Dal momento che il tasto JSON è archiviato in un testo standard, non vi sono garanzie che i valori archiviati nelle colonne di testo siano formattate correttamente.Since JSON is stored in a standard text, there are no guarantees that the values stored in text columns are properly formatted. È possibile verificare che il testo archiviato nella colonna JSON sia formattato correttamente usando i vincoli di controllo standard del database SQL di Azure e la funzione ISJSON:You can verify that text stored in JSON column is properly formatted by using standard Azure SQL Database check constraints and the ISJSON function:

ALTER TABLE Products
    ADD CONSTRAINT [Data should be formatted as JSON]
        CHECK (ISJSON(Data) > 0)

Se il testo di input è formattato correttamente come JSON, la funzione ISJSON restituisce il valore 1.If the input text is properly formatted JSON, the ISJSON function returns the value 1. A ogni inserimento o aggiornamento della colonna JSON, questo vincolo verificherà che nuovo valore di testo non sia un formato JSON non corretto.On every insert or update of JSON column, this constraint will verify that new text value is not malformed JSON.

Trasformazione del formato JSON in formato tabulareTransforming JSON into tabular format

Il database SQL di Azure consente anche di trasformare gli insiemi JSON in formato tabulare e di caricare o eseguire query sui dati JSON.Azure SQL Database also lets you transform JSON collections into tabular format and load or query JSON data.

OPENJSON è una funzione con valori di tabella che analizza il testo JSON, individua un array di oggetti JSON, scorre gli elementi dell'array e restituisce una riga nel risultato di output per ogni elemento dell'array.OPENJSON is a table-value function that parses JSON text, locates an array of JSON objects, iterates through the elements of the array, and returns one row in the output result for each element of the array.

JSON tabulare

Nell'esempio precedente, è possibile specificare dove si trova l'array JSON da aprire (nel percorso $ Orders), quali colonne devono essere restituite come risultato e dove trovare i valori JSON che verranno restituiti come celle.In the example above, we can specify where to locate the JSON array that should be opened (in the $.Orders path), what columns should be returned as result, and where to find the JSON values that will be returned as cells.

È possibile trasformare un array JSON nella variabile @orders in un set di righe, analizzare il set di risultati o inserire righe in una tabella standard:We can transform a JSON array in the @orders variable into a set of rows, analyze this result set, or insert rows into a standard table:

CREATE PROCEDURE InsertOrders(@orders nvarchar(max))
AS BEGIN

    insert into Orders(Number, Date, Customer, Quantity)
    select Number, Date, Customer, Quantity
    OPENJSON (@orders)
     WITH (
            Number varchar(200),
            Date datetime,
            Customer varchar(200),
            Quantity int
     )

END

La raccolta di ordini formattata come array JSON e fornita come parametro alla stored procedure può essere analizzata e inserita nella tabella Orders.The collection of orders formatted as a JSON array and provided as a parameter to the stored procedure can be parsed and inserted into the Orders table.

Passaggi successiviNext steps

Per informazioni su come integrare JSON nell'applicazione, consultare le seguenti risorse:To learn how to integrate JSON into your application, check out these resources:

Per altre informazioni sui vari scenari per l'integrazione di JSON nell'applicazione, vedere le dimostrazioni in questo video di Channel 9 o trovare uno scenario corrispondente al caso d'uso nei post del blog su JSON.To learn about various scenarios for integrating JSON into your application, see the demos in this Channel 9 video or find a scenario that matches your use case in JSON Blog posts.