Aprile 2017

Volume 32 Numero 4

Il presente articolo è stato tradotto automaticamente.

Cutting Edge - Query dei dati JSON in SQL Server 2016

Da Dino Esposito | 2017 aprile

Dino EspositoSpostamento di dati in sistemi indipendenti e autonomi è che la maggior parte dei software esegue questi giorni e JSON è il linguaggio universale dietro il trasferimento dei dati. Acronimo di JavaScript Object Notation, JSON è un sistema basato su testo per preparare lo stato di un oggetto in modo che possa essere facilmente serializzato e trasferito attraverso la rete da un sistema a quella successiva, in particolare nei sistemi eterogenei.

JSON è diventato cosa, alla fine, XML non è riuscito, ovvero la lingua franca del Web. Personalmente, non sarebbe acquistare in gran parte al fatto che JSON è più facile da leggere rispetto a XML. D'altra parte, JSON è un formato di testo molto più compatto e leggero di XML, modificabili dagli utenti e rapido analizzare e comprendere per i computer in un lungo elenco di piattaforme hardware e software.

Una stringa JSON è una stringa di testo normale e tutte le versioni di qualsiasi sistema di gestione di database relazionali (RDBMS), tra cui SQL Server, consentono di archiviare una stringa indipendentemente dal relativo layout del contenuto. SQL Server 2016, tuttavia, è la prima versione del database di Microsoft che consente di leggere i dati tabulari esistenti come JSON, per salvare i dati tabulari in formato JSON e, cosa più importante per eseguire una query all'interno di stringhe JSON come se il contenuto JSON sono stati effettivamente una raccolta di singole colonne.

Per una panoramica completa e non strutturati delle funzioni JSON in SQL Server 2016, leggere la documentazione di MSDN all'indirizzo bit.ly/2llab1n. È inoltre possibile trovare un ottimo riepilogo di JSON in SQL Server 2016 nell'articolo parlare semplice in bit.ly/26rprwv. L'articolo offre una visualizzazione più orientati al business di JSON in SQL Server 2016 e, in generale, un punto di vista basato su scenari di utilizzo di dati JSON in un livello di persistenza relazionale.

Dati JSON nel livello di persistenza

Due verbi sono fondamentali per comprendere lo scopo di JSON: la trasmissione e la serializzazione. Pertanto, JSON è il formato in cui impostare lo stato di un'entità software in modo che possono essere trasmessi tra spazi di processo con la certezza che sarà ben comprese in entrambe le estremità. Grande, ma questa è una colonna su JSON in SQL Server e, di conseguenza, nel livello di persistenza. Quindi, iniziamo con la domanda di base: Quando si è salvare dati in SQL Server nel formato JSON?

Una tabella di database relazionale è articolata in un numero fisso di colonne e ogni colonna ha un proprio tipo di dati, ad esempio le stringhe di variabile o lunghezza fissa, date, numeri, valori booleani e così via. JSON non è un tipo di dati nativi. Una colonna di SQL Server che contiene i dati JSON dal punto di vista di database è una colonna stringa di testo normale. È possibile scrivere dati JSON per una colonna di tabella come scrivere una stringa normale e che è possibile eseguire in qualsiasi versione di SQL Server, nonché in qualsiasi altro sistema RDBMS.

In cui si ottengono le stringhe JSON che infine archiviare in un database? Esistono due scenari principali: In primo luogo, tali stringhe potrebbero provenire da un servizio Web o un'altra forma di un endpoint esterno che trasmette i dati (ad esempio, un dispositivo connesso o sensori). In secondo luogo, i dati JSON potrebbero essere un modo pratico per raggruppare elementi correlati di informazioni in modo che vengano visualizzati come un singolo elemento dati. Questo accade solitamente quando si gestiscono i dati semistrutturati, ad esempio i dati che rappresenta un evento di business di archiviare in uno scenario di origine evento o, più semplicemente, in un contesto aziendale è intrinsecamente basata sugli eventi, ad esempio i sistemi in tempo reale per i domini, ad esempio contabilità, commerciali, assegnazione dei punteggi, monitoraggio, automazione industriale e controllo e così via. In questi casi, lo spazio di archiviazione può essere normalizzata in un form strutturato la serializzazione di lunghezza variabile informazioni correlate e il formato in un singolo elemento dati che è possibile inserire nella colonna stringa di una tabella relazionale.

Come accennato, è possibile mantenere il contenuto JSON può provenire da un'origine esterna o può essere generato mediante la serializzazione delle istanze di oggetti c#:

foreach (var c in countries)
{
  // Serialize the C# object to JSON
  var json = JsonConvert.SerializeObject(c);
  // Save content to the database
  record.JsonColumn = json;
}

È possibile utilizzare Entity Framework (EF), nonché, per salvare i dati JSON in una colonna di una tabella di database.

SQL Server 2016 accetta ulteriormente questo livello e consente di trasformare i dati JSON in righe di tabella. Questa possibilità è possibile risparmiare parecchio lavoro e cicli della CPU del codice, ora è possibile inserire il testo JSON non elaborato al database senza prima analisi a oggetti c# nel codice dell'applicazione e quindi si passa tramite Entity Framework o le chiamate dirette di ADO.NET. La chiave per raggiungere questo obiettivo è la nuova funzione OPENJSON:

    declare @country nvarchar(max) = '{
      "id" : 101,
      "name": "United States",
      "continent": "North America"
    }';
      INSERT INTO Countries
        SELECT * FROM OPENJSON(@country)
        WITH (id int,
          name nvarchar(100),
          continent nvarchar(100))

È possibile utilizzare la funzione per inserire o aggiornare le righe di tabella regolare dal testo JSON normale. La clausola WITH consente di eseguire il mapping di proprietà JSON alle colonne della tabella esistente.

Lo Scenario di origine evento

Nel mio articolo di dicembre 2016, discusso origine evento come un modello emergente per archiviare lo stato cronologico dell'applicazione (msdn.com/magazine/mt790196). Anziché salvare lo stato valido noto più recente, con origine eventi salvare tutti gli eventi di business che modifica lo stato e ricompilare lo stato più recente di riproduzione di eventi passati.

L'aspetto fondamentale di un'implementazione di eventi di approvvigionamento è sull'efficienza è possibile salvare e recuperare gli eventi passati. Ogni evento è diversa e potrebbe essere uno schema diverso, a seconda del tipo e informazioni disponibili. Allo stesso tempo, con un archivio (relazionale) distinto per ogni tipo di evento è problematica perché gli eventi forniti in modo asincrono e potrebbero influire su diverse entità e segmenti diversi dello stato. Se collocate in tabelle diverse, la ricompilazione lo stato potrebbe diventare costosa a causa di join tra tabelle. Di conseguenza, il salvataggio di eventi come oggetti è più consigliabile e archivi NoSQL svolgono il lavoro molto bene. È possibile effettuare acquisti di eventi con un database relazionale invece?

Salvataggio dell'evento come JSON è un'opzione possibile in qualsiasi versione di SQL Server, ma la lettura di JSON in effetti, quando un numero elevato di eventi si trovino nell'archivio, potrebbe essere non sostenibile. Con le caratteristiche JSON native di SQL Server 2016, le modifiche orizzontale e utilizzo di SQL Server in uno scenario di origine evento diventa realistici. Tuttavia, come la query JSON da una tabella di database?

Eseguire query sui dati all'esterno di contenuto JSON

Quindi, supponiamo che si gestito in modo da avere una o più colonne di dati JSON in una tabella relazionale canonica. Di conseguenza, le colonne con dati primitivi e le colonne popolate con i dati JSON live side-by-side. A meno che non vengono utilizzate le nuove funzioni di SQL Server 2016, le colonne JSON vengono considerate come campi di testo normale e possono eseguire una query solo con T-SQL stringa e testo le istruzioni, ad esempio quali SUBSTRING e TRIM. Ai fini della dimostrazione, ho creato una colonna denominata paesi, ovvero con poche colonne tabulari, e un altro denominato serializzato che contiene il resto intero del record serializzato come JSON, come illustrato figura 1.

Il Database di esempio paesi con una colonna JSON
Figura 1, il Database di esempio paesi con una colonna JSON

L'oggetto JSON serializzato nella tabella di esempio è simile al seguente:

{
  "CountryCode":"AD",
  "CountryName":"Andorra",
  "CurrencyCode":"EUR",
  "Population":"84000",
  "Capital":"Andorra la Vella",
  "ContinentName":"Europe",
  "Continent":"EU",
  "AreaInSqKm":"468.0",
  "Languages":"ca",
  "GeonameId":"3041565",
  "Cargo":null

La query T-SQL seguente viene illustrato come selezionare solo i paesi che il conteggio di più di 100 milione di abitanti. La query sono presenti colonne di tabella regolari e proprietà JSON:

SELECT CountryCode,
  CountryName,
  JSON_VALUE(Serialized, '$.Population') AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized, '$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

La funzione JSON_VALUE accetta il nome di una colonna JSON (o una variabile locale impostata su una stringa JSON) ed estrae il valore scalare che segue il percorso specificato. Come illustrato nella figura 2, il simbolo $ fa riferimento alla radice dell'oggetto JSON serializzato.

Risultati di una Query JSON
Figura 2 risultati di una Query JSON

Poiché la colonna di JSON è configurata come una normale colonna NVARCHAR, desiderato utilizzare la funzione ISJSON per controllare se il contenuto della colonna è JSON reale. La funzione restituisce un valore positivo se il contenuto è JSON.

JSON_VALUE restituisce sempre una stringa di un massimo di 4.000 byte, indipendentemente dalla proprietà selezionata. Se si prevede che un valore restituito più lungo, è consigliabile usare OPENJSON invece. In ogni caso, è possibile prendere in considerazione un CAST per ottenere un valore del tipo appropriato. Tornando all'esempio precedente, si supponga il numero di persone che vivono in un paese, formattato con virgole. (In generale, questo potrebbe non essere una buona idea poiché formattazione di dati nel livello di presentazione fornisce codice maggiore flessibilità.) La funzione FORMAT SQL prevede di ricevere un numero e viene visualizzato un errore se si passa il valore JSON diretto. Per renderlo lavoro, è necessario ricorrere a un CAST esplicito:

    SELECT CountryCode,
      CountryName,
      FORMAT(CAST(
        JSON_VALUE(Serialized, '$.Population') AS int), 'N0')
        AS People
    FROM Countries
    WHERE ISJSON(Serialized) > 0 AND
      JSON_VALUE(Serialized,'$.Population') > 100000000
    ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

JSON_VALUE può restituire solo un singolo valore scalare. Se si dispone di una matrice di un oggetto nidificata che si desidera estrarre, è necessario ricorrere alla funzione JSON_QUERY.

Valutare l'efficienza è possibile eseguire query sui dati JSON? Procediamo alcuni test.

Indicizzazione del contenuto JSON in SQL Server 2016

Come è ovvio come potrebbe sembrare, l'intera stringa JSON dal database di query e quindi analizzarlo in memoria tramite una libreria dedicata, ad esempio JSON di Newtonsoft, sebbene sempre funzionale, potrebbe non essere un approccio efficace in tutti i casi. Efficacia dipende principalmente il numero di record nel database e quanto tempo potrebbe effettivamente necessario per ottenere i dati che necessari nel formato desiderato. Probabilmente per una query che l'applicazione viene eseguita in alcuni casi, l'elaborazione in memoria dei dati JSON potrebbe comunque essere un'opzione. In generale, tuttavia, esegue una query tramite funzioni dedicato JSON e lasciare che SQL Server eseguire l'analisi internamente genera codice leggermente più veloce. La differenza è ancora maggiore se si aggiunge un indice in dati JSON.

È consigliabile evitare di creare l'indice sulla colonna JSON, tuttavia, come sarebbe indicizzare il valore JSON come una singola stringa. Sarà difficilmente una query per l'intera stringa JSON o un suo subset. In modo più realistico, invece, sarà una query per il valore di una determinata proprietà nell'oggetto JSON serializzato. Un approccio più efficace è la creazione di uno o più colonne in base al valore di una o più proprietà JSON e quindi l'indicizzazione delle colonne calcolate. Di seguito è riportato un esempio in T-SQL:

    -- Add a computed column
    ALTER TABLE dbo.Countries
    ADD JsonPopulation
    AS JSON_VALUE(Serialized, '$.Population')
    -- Create an index
    CREATE INDEX IX_Countries_JsonPopulation
    ON dbo.Countries(JsonPopulation)

Anche in questo caso, è necessario essere consapevoli che JSON_VALUE restituisce NVARCHAR, a meno che non si aggiunge CAST l'indice verrà creato nel testo.

È interessante notare che l'analisi JSON è più la deserializzazione di alcuni tipi speciali, ad esempio XML e spaziali veloce. È possibile trovare altre informazioni, vedere bit.ly/2kthrrC. In sintesi, almeno l'analisi JSON è meglio il recupero delle proprietà di altri tipi.

JSON ed Entity Framework

In linea generale, il supporto JSON in SQL Server 2016 è esposta principalmente tramite la sintassi T-SQL, come strumenti sono piuttosto limitato. In particolare, Entity Framework attualmente non fornisce funzionalità per eseguire query sui dati JSON, ad eccezione del metodo SqlQuery in Entity Framework 6 e FromSql in Entity Framework Core. Tuttavia, ciò non significa che è Impossibile serializzare le proprietà complesse di classi c# (ad esempio, matrici) in colonne JSON. Un'esercitazione eccellente per Core di Entity Framework è reperibile in bit.ly/2kVEsam.

Conclusioni

SQL Server 2016 introduce alcune funzionalità JSON nativa in modo che è possibile richiedere in modo più efficace i dati JSON archiviati come set di righe canonico. Ciò si verifica soprattutto quando i dati JSON sono la versione serializzata di alcuni semistrutturati aggregazione dei dati. Gli indici creati all'esterno delle colonne calcolate che riflettono il valore di una o più proprietà JSON è senz'altro utile migliorare le prestazioni.

I dati JSON sono archiviati come testo normale e non sono considerati un tipo speciale, ad esempio Spatial e XML. Tuttavia, questo appena consente di utilizzare immediatamente le colonne JSON in oggetti di SQL Server. Lo stesso può dire per altri tipi complessi, ad esempio XML, CLR e spaziale che sono ancora presenti nell'elenco di attesa.

In questo articolo, mi sono concentrato sullo scenario di JSON al set di righe. Tuttavia, SQL Server 2016 supporta pienamente lo scenario di query di set di righe a JSON durante la scrittura di una normale query T-SQL e quindi eseguire il mapping dei risultati per gli oggetti JSON tramite la clausola FOR JSON. Per ulteriori informazioni su questa funzionalità, vedere bit.ly/2fTKly7.


Dino Espositoè l'autore di "Microsoft .NET:  Architettura delle applicazioni per l'azienda"(Microsoft Press, 2014) e"Moderne applicazioni Web con ASP.NET"(Microsoft Press, 2016). Technical evangelist per .NET e a piattaforme Android al JetBrains e spesso come relatore a eventi del settore in tutto il mondo, Esposito condivide la sua visione del software in software2cents.wordpress.com e su Twitter: @despos.

Grazie al seguente esperto tecnico Microsoft per la revisione dell'articolo: Jovan Popovic