Indicizzazione dei dati JSONIndex JSON data

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 SQL Server 2016 JSON non è un tipo di dati predefinito e SQL Server non usa indici JSON personalizzati.In SQL Server 2016, JSON is not a built-in data type, and SQL Server does not have custom JSON indexes. È possibile tuttavia ottimizzare le query sui documenti JSON usando indici standard.You can optimize your queries over JSON documents, however, by using standard indexes.

Gli indici del database migliorano le prestazioni delle operazioni di filtro e ordinamento.Database indexes improve the performance of filter and sort operations. Senza indici, SQL Server deve eseguire un'analisi completa della tabella ogni volta che viene eseguita una query sui dati.Without indexes, SQL Server has to perform a full table scan every time you query data.

Indicizzazione delle proprietà JSON mediante colonne calcolateIndex JSON properties by using computed columns

Quando si archiviano i dati JSON in SQL Server, di solito l'obiettivo è filtrare o ordinare i risultati delle query in base a una o più proprietà dei documenti JSON.When you store JSON data in SQL Server, typically you want to filter or sort query results by one or more properties of the JSON documents.

EsempioExample

In questo esempio si suppone che la tabella SalesOrderHeader di AdventureWorks includa una colonna Info che contiene varie informazioni in formato JSON sugli ordini di vendita.In this example, assume that the AdventureWorks SalesOrderHeader table has an Info column that contains various information in JSON format about sales orders. Contiene ad esempio informazioni sul cliente, sul venditore, sugli indirizzi di spedizione e di fatturazione e così via.For example, it contains information about customer, sales person, shipping and billing addresses, and so forth. Si vogliono usare i valori della colonna Info per filtrare gli ordini di vendita per un cliente.You want to use values from the Info column to filter sales orders for a customer.

Query da ottimizzareQuery to optimize

Di seguito è riportato un esempio del tipo di query da ottimizzare usando un indice.Here's an example of the type of query that you want to optimize by using an index.

SELECT SalesOrderNumber,
    OrderDate,
    JSON_VALUE(Info, '$.Customer.Name') AS CustomerName
FROM Sales.SalesOrderHeader
WHERE JSON_VALUE(Info, '$.Customer.Name') = N'Aaron Campbell' 

Indice di esempioExample index

Per velocizzare i filtri o le clausole ORDER BY su una proprietà in un documento JSON, è possibile usare gli stessi indici già usati per altre colonne.If you want to speed up your filters or ORDER BY clauses over a property in a JSON document, you can use the same indexes that you're already using on other columns. Tuttavia, non è possibile fare direttamente riferimento alle proprietà nei documenti JSON.However, you can't directly reference properties in the JSON documents.

  1. Per prima cosa è necessario creare una "colonna virtuale" che restituisca i valori che verranno usati per il filtro.First, you have to create a "virtual column" that returns the values that you want to use for filtering.
  2. È quindi necessario creare un indice su quella colonna virtuale.Then you have to create an index on that virtual column.

Nell'esempio seguente viene creata una colonna calcolata che può essere usata per l'indicizzazione.The following example creates a computed column that can be used for indexing. Successivamente viene creato un indice per la nuova colonna calcolata.Then it creates an index on the new computed column. In questo esempio viene creata una colonna che espone il nome del cliente archiviato nel percorso $.Customer.Name nei dati JSON.This example creates a column that exposes the customer name, which is stored in the $.Customer.Name path in the JSON data.

ALTER TABLE Sales.SalesOrderHeader
ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name')

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)  

Altre informazioni sulla colonna calcolataMore info about the computed column

La colonna calcolata non è persistente.The computed column is not persisted. È calcolata solo quando è necessario ricostruire l'indice.It's computed only when the index needs to be rebuilt. Non occupa spazio aggiuntivo nella tabella.It does not occupy additional space in the table.

È importante creare la colonna calcolata con la stessa espressione che si prevede di usare nelle query, in questo esempio l'espressione è JSON_VALUE(Info, '$.Customer.Name').It's important that you create the computed column with the same expression that you plan to use in your queries - in this example, the expression is JSON_VALUE(Info, '$.Customer.Name').

Non è necessario riscrivere le query.You don’t have to rewrite your queries. Se si usano espressioni con la funzione JSON_VALUE, come illustrato nella query precedente, SQL Server rileva la presenza di una colonna calcolata equivalente con la stessa espressione e, se possibile, applica un indice.If you use expressions with the JSON_VALUE function, as shown in the example query above, SQL Server sees that there's an equivalent computed column with the same expression and applies an index if possible.

Piano di esecuzione per questo esempioExecution plan for this example

Di seguito viene riportato il piano di esecuzione per la query di questo esempio.Here's the execution plan for the query in this example.

Piano di esecuzioneExecution plan

Anziché un'analisi completa della tabella, SQL Server usa un indice seek nell'indice non cluster e individua le righe che soddisfano le condizioni specificate.Instead of a full table scan, SQL Server uses an index seek into the non-clustered index and finds the rows that satisfy the specified conditions. Usa quindi una ricerca chiave nella tabella SalesOrderHeader per recuperare le altre colonne a cui si fa riferimento nella query, in questo esempio, SalesOrderNumber e OrderDate.Then it uses a key lookup in the SalesOrderHeader table to fetch the other columns that are referenced in the query - in this example, SalesOrderNumber and OrderDate.

Ottimizzare ulteriormente l'indice con colonne incluseOptimize the index further with included columns

Se si aggiungono le colonne richieste nell'indice, è possibile evitare questa ulteriore ricerca nella tabella.You can avoid this additional lookup in the table if you add required columns in the index. È possibile aggiungere tali colonne come colonne incluse standard, come illustrato nell'esempio seguente, che estende l'esempio CREATE INDEX riportato in precedenza.You can add these columns as standard included columns, as shown in the following example, which extends the CREATE INDEX example shown above.

CREATE INDEX idx_soh_json_CustomerName
ON Sales.SalesOrderHeader(vCustomerName)
INCLUDE(SalesOrderNumber,OrderDate)

In questo caso SQL Server non deve leggere i dati aggiuntivi della tabella SalesOrderHeader perché tutto il necessario è incluso nell'indice non cluster JSON.In this case SQL Server doesn't have to read additional data from the SalesOrderHeader table because everything it needs is included in the non-clustered JSON index. Questo è un buon metodo per combinare i dati JSON e di colonna nelle query e per creare indici ottimali per il carico di lavoro.This is a good way to combine JSON and column data in queries and to create optimal indexes for your workload.

Gli indici JSON sono in grado di riconoscere le regole di confrontoJSON indexes are collation-aware indexes

Una caratteristica importante degli indici rispetto ai dati JSON è che gli indici sono in grado di riconoscere le regole di confronto.An important feature of indexes over JSON data is that the indexes are collation-aware. Il risultato della funzione JSON_VALUE, che si usa quando si crea la colonna calcolata, è un valore di testo che eredita le regole di confronto dall'espressione di input.The result of the JSON_VALUE function that you use when you create the computed column is a text value that inherits its collation from the input expression. Di conseguenza, i valori dell'indice vengono ordinati usando le regole di confronto definite nelle colonne di origine.Therefore, values in the index are ordered using the collation rules defined in the source columns.

Per dimostrare questo concetto, nell'esempio seguente viene creata una semplice tabella di raccolta insieme con una chiave primaria e contenuto JSON.To demonstrate this, the following example creates a simple collection table with a primary key and JSON content.

CREATE TABLE JsonCollection
 (
  id INT IDENTITY CONSTRAINT PK_JSON_ID PRIMARY KEY,
  json NVARCHAR(MAX) COLLATE SERBIAN_CYRILLIC_100_CI_AI
  CONSTRAINT [Content should be formatted as JSON]
  CHECK(ISJSON(json)>0)
 ) 

Il comando precedente consente di specificare le regole di confronto per il serbo (cirillico) per la colonna JSON.The preceding command specifies the Serbian Cyrillic collation for the JSON column. Nell'esempio seguente la tabella viene popolata e viene creato un indice nella proprietà del nome.The following example populates the table and creates an index on the name property.

INSERT INTO JsonCollection
VALUES
(N'{"name":"Иво","surname":"Андрић"}'),
(N'{"name":"Андрија","surname":"Герић"}'),
(N'{"name":"Владе","surname":"Дивац"}'),
(N'{"name":"Новак","surname":"Ђоковић"}'),
(N'{"name":"Предраг","surname":"Стојаковић"}'),
(N'{"name":"Михајло","surname":"Пупин"}'),
(N'{"name":"Борислав","surname":"Станковић"}'),
(N'{"name":"Владимир","surname":"Грбић"}'),
(N'{"name":"Жарко","surname":"Паспаљ"}'),
(N'{"name":"Дејан","surname":"Бодирога"}'),
(N'{"name":"Ђорђе","surname":"Вајферт"}'),
(N'{"name":"Горан","surname":"Бреговић"}'),
(N'{"name":"Милутин","surname":"Миланковић"}'),
(N'{"name":"Никола","surname":"Тесла"}')
GO

ALTER TABLE JsonCollection
ADD vName AS JSON_VALUE(json,'$.name')

CREATE INDEX idx_name
ON JsonCollection(vName)

I comandi precedenti creano un indice standard per la colonna calcolata vName, che rappresenta il valore della proprietà $.name JSON.The preceding commands create a standard index on the computed column vName, which represents the value from the JSON $.name property. Nella tabella codici per il serbo (cirillico), l'ordine delle lettere è ‘А’,’Б’,’В’,’Г’,’Д’,’Ђ’,’Е’, ecc. L'ordine degli elementi nell'indice è conforme alle regole per il serbo (cirillico) perché il risultato della funzione JSON_VALUE eredita le regole di confronto dalla colonna di origine.In the Serbian Cyrillic code page, the order of the letters is ‘А’,’Б’,’В’,’Г’,’Д’,’Ђ’,’Е’, etc. The order of items in the index is compliant with Serbian Cyrillic rules because the result of the JSON_VALUE function inherits its collation from the source column. Nell'esempio seguente viene eseguita una query su questa raccolta e i risultati vengono ordinati in base al nome.The following example queries this collection and sorts the results by name.

SELECT JSON_VALUE(json,'$.name'),*
FROM JsonCollection
ORDER BY JSON_VALUE(json,'$.name')

Se si esamina il piano di esecuzione, si noterà che vengono usati valori ordinati dall'indice non cluster.If you look at the actual execution plan, you see that it uses sorted values from the non-clustered index.

Piano di esecuzioneExecution plan

Anche se la query include una clausola ORDER BY, il piano di esecuzione non usa un operatore di ordinamento.Although the query has an ORDER BY clause, the execution plan doesn't use a Sort operator. L'indice JSON è già ordinato in base alle regole per il serbo (cirillico).The JSON index is already ordered according to Serbian Cyrillic rules. SQL Server può pertanto usare l'indice non cluster in cui risultati sono già ordinati.Therefore SQL Server can use the non-clustered index where results are already sorted.

Tuttavia, se si modificano le regole di confronto dell'espressione ORDER BY, ad esempio inserendo COLLATE French_100_CI_AS_SC dopo la funzione JSON_VALUE, si ottiene un piano di esecuzione della query differente.However, if we change collation of the ORDER BY expression - for example, if we put COLLATE French_100_CI_AS_SC after the JSON_VALUE function - we get a different query execution plan.

Piano di esecuzioneExecution plan

Poiché l'ordine dei valori in corrispondenza dell'indice non è conforme alle regole di confronto per il francese, SQL Server non può usare l'indice per ordinare i risultati.Since the order of values in the index is not compliant with French collation rules, SQL Server can't use the index to order results. Pertanto, aggiunge un operatore di ordinamento che ordina i risultati usando le regole di confronto per il francese.Therefore, it adds a Sort operator that sorts results using French collation rules.

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.