Convalidare, eseguire query e modificare i dati JSON con funzioni predefinite (SQL Server)Validate, Query, and Change JSON Data with Built-in Functions (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

Il supporto integrato per JSON include le funzioni predefinite seguenti descritte brevemente in questo argomento.The built-in support for JSON includes the following built-in functions described briefly in this topic.

  • ISJSON verifica se una stringa include contenuto JSON valido.ISJSON tests whether a string contains valid JSON.

  • JSON_VALUE estrae un valore scalare da una stringa JSON.JSON_VALUE extracts a scalar value from a JSON string.

  • JSON_QUERY estrae un oggetto o una matrice da una stringa JSON.JSON_QUERY extracts an object or an array from a JSON string.

  • JSON_MODIFY aggiorna il valore di una proprietà in una stringa JSON e restituisce la stringa JSON aggiornata.JSON_MODIFY updates the value of a property in a JSON string and returns the updated JSON string.

Testo JSON per gli esempi in questa paginaJSON text for the examples on this page

Gli esempi in questa pagina usano il testo JSON seguente, che contiene un elemento complesso.The examples on this page use the following JSON text, which contains a complex element.

DECLARE @jsonInfo NVARCHAR(MAX)

SET @jsonInfo=N'{  
     "info":{    
       "type":1,  
       "address":{    
         "town":"Bristol",  
         "county":"Avon",  
         "country":"England"  
       },  
       "tags":["Sport", "Water polo"]  
    },  
    "type":"Basic"  
 }' 

Convalidare il testo JSON tramite la funzione ISJSON Validate JSON text by using the ISJSON function

La funzione ISJSON verifica se una stringa include contenuto JSON valido.The ISJSON function tests whether a string contains valid JSON.

L'esempio seguente restituisce le righe in cui la colonna json_col include contenuto JSON valido.The following example returns rows in which the column json_col contains valid JSON.

SELECT id, json_col
FROM tab1
WHERE ISJSON(json_col) > 0 

Per altre informazioni, vedere ISJSON (Transact-SQL).For more info, see ISJSON (Transact-SQL).

Estrarre un valore dal testo JSON tramite la funzione JSON_VALUE Extract a value from JSON text by using the JSON_VALUE function

La funzione JSON_VALUE estrae un valore scalare da una stringa JSON.The JSON_VALUE function extracts a scalar value from a JSON string.

Nell'esempio seguente viene estratto il valore della proprietà JSON annidata town in una variabile locale.The following example extracts the value of the nested JSON property town into a local variable.

SET @town = JSON_VALUE(@jsonInfo, '$.info.address.town')  

Per altre informazioni, vedere JSON_VALUE (Transact-SQL).For more info, see JSON_VALUE (Transact-SQL).

Estrarre un oggetto o una matrice dal testo JSON tramite la funzione JSON_QUERY Extract an object or an array from JSON text by using the JSON_QUERY function

La funzione JSON_QUERY estrae un oggetto o una matrice da una stringa JSON.The JSON_QUERY function extracts an object or an array from a JSON string.

Nell'esempio seguente viene illustrato come restituire un frammento JSON nei risultati della query.The following example shows how to return a JSON fragment in query results.

SELECT FirstName, LastName, JSON_QUERY(jsonInfo,'$.info.address') AS Address
FROM Person.Person
ORDER BY LastName

Per altre informazioni, vedere JSON_QUERY (Transact-SQL).For more info, see JSON_QUERY (Transact-SQL).

Confronto tra JSON_VALUE e JSON_QUERY Compare JSON_VALUE and JSON_QUERY

La differenza principale tra JSON_VALUE e JSON_QUERY consiste nel fatto che JSON_VALUE restituisce un valore scalare, mentre JSON_QUERY restituisce un oggetto o una matrice.The key difference between JSON_VALUE and JSON_QUERY is that JSON_VALUE returns a scalar value, while JSON_QUERY returns an object or an array.

Si consideri il testo JSON di esempio seguente.Consider the following sample JSON text.

{
    "a": "[1,2]",
    "b": [1, 2],
    "c": "hi"
}  

In questo testo JSON di esempio i membri dati "a" e "c" sono valori stringa, mentre il membro dati "b" è una matrice.In this sample JSON text, data members "a" and "c" are string values, while data member "b" is an array. JSON_VALUE e JSON_QUERY restituiscono i risultati seguenti:JSON_VALUE and JSON_QUERY return the following results:

PercorsoPath JSON_VALUE restituisceJSON_VALUE returns JSON_QUERY restituisceJSON_QUERY returns
$ NULL o erroreNULL or error { "a": "[1,2]", "b": [1,2], "c":"hi"}
$.a$.a [1,2][1,2] NULL o erroreNULL or error
$.b$.b NULL o erroreNULL or error [1,2][1,2]
$.b[0]$.b[0] 11 NULL o erroreNULL or error
$.c$.c hihi NULL o erroreNULL or error

Test di JSON_VALUE e JSON_QUERY con il database di esempio AdventureWorksTest JSON_VALUE and JSON_QUERY with the AdventureWorks sample database

Testare le funzioni predefinite descritte in questo argomento tramite l'esecuzione degli esempi seguenti con il database di esempio AdventureWorks, contenente i dati JSON.Test the built-in functions described in this topic by running the following examples with the AdventureWorks sample database, which contains JSON data. Per ottenere il database di esempio AdventureWorks, fare clic qui.To get the AdventureWorks sample database, click here.

Negli esempi seguenti la colonna Info nella tabella SalesOrder_json contiene testo JSON.In the following examples, the Info column in the SalesOrder_json table contains JSON text.

Esempio 1: restituire colonne standard e dati JSONExample 1 - Return both standard columns and JSON data

La query seguente restituisce valori sia dalle colonne relazionali standard sia da una colonna JSON.The following query returns values from both standard relational columns and from a JSON column.

SELECT SalesOrderNumber, OrderDate, Status, ShipDate, Status, AccountNumber, TotalDue,
 JSON_QUERY(Info,'$.ShippingInfo') ShippingInfo,
 JSON_QUERY(Info,'$.BillingInfo') BillingInfo,
 JSON_VALUE(Info,'$.SalesPerson.Name') SalesPerson,
 JSON_VALUE(Info,'$.ShippingInfo.City') City,
 JSON_VALUE(Info,'$.Customer.Name') Customer,
 JSON_QUERY(OrderItems,'$') OrderItems
FROM Sales.SalesOrder_json
WHERE ISJSON(Info) > 0

Esempio 2: aggregare e filtrare valori JSONExample 2- Aggregate and filter JSON values

La query seguente aggrega i subtotali in base al nome del cliente (archiviato in JSON) e in base allo stato (archiviato in una colonna normale),The following query aggregates subtotals by customer name (stored in JSON) and status (stored in an ordinary column). quindi filtra i risultati in base alla città (archiviata in JSON) e in base al valore OrderDate (archiviato in una colonna normale).Then it filters the results by city (stored in JSON) and OrderDate (stored in an ordinary column).

DECLARE @territoryid INT;
DECLARE @city NVARCHAR(32);

SET @territoryid=3;

SET @city=N'Seattle';

SELECT JSON_VALUE(Info, '$.Customer.Name') AS Customer, Status, SUM(SubTotal) AS Total
FROM Sales.SalesOrder_json
WHERE TerritoryID=@territoryid
 AND JSON_VALUE(Info, '$.ShippingInfo.City') = @city
 AND OrderDate > '1/1/2015'
GROUP BY JSON_VALUE(Info, '$.Customer.Name'), Status
HAVING SUM(SubTotal)>1000

Aggiornare i valori delle proprietà in testo JSON tramite la funzione JSON_MODIFY Update property values in JSON text by using the JSON_MODIFY function

La funzione JSON_MODIFY aggiorna il valore di una proprietà in una stringa JSON e restituisce la stringa JSON aggiornata.The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.

Nell'esempio seguente viene aggiornato il valore di una proprietà JSON in una variabile che include contenuto JSON.The following example updates the value of a JSON property in a variable that contains JSON.

SET @info = JSON_MODIFY(@jsonInfo, "$.info.address[0].town", 'London')    

Per altre informazioni, vedere JSON_MODIFY (Transact-SQL).For more info, see JSON_MODIFY (Transact-SQL).

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.

Vedere ancheSee Also

ISJSON (Transact-SQL) ISJSON (Transact-SQL)
JSON_VALUE (Transact-SQL) JSON_VALUE (Transact-SQL)
JSON_QUERY (Transact-SQL) JSON_QUERY (Transact-SQL)
JSON_MODIFY (Transact-SQL) JSON_MODIFY (Transact-SQL)
JSON Path Expressions (Espressioni di percorso JSON) (SQL Server)JSON Path Expressions (SQL Server)