Microsoft SQL Server
Query con dati JSON

Utilizzo dei valori da JSON nella query

La funzione JSON_VALUE consente di acquisire dati dal testo JSON sul percorso specificato come secondo argomento e utilizzare questo valore in qualsiasi parte della query di selezione:

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'

Utilizzo dei valori JSON nei report

Una volta estratti i valori JSON dal testo JSON, è possibile utilizzarli in qualsiasi parte della query. È possibile creare un tipo di rapporto sui dati JSON con aggregazioni di raggruppamento, ecc.

select JSON_VALUE(Data, '$.Type') as type,
        AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost
from Product
group by JSON_VALUE(Data, '$.Type') 
having JSON_VALUE(Data, '$.Type') is not null

Filtraggio del testo JSON non valido dai risultati della query

Se alcuni testi JSON potrebbero non essere formattati correttamente, è possibile rimuovere tali voci dalla query utilizzando la funzione ISJSON.

select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type
from Product
where JSON_VALUE(Data, '$.Type') = 'part'
and ISJSON(Data) > 0

Aggiorna valore nella colonna JSON

La funzione JSON_MODIFY può essere utilizzata per aggiornare il valore su qualche percorso. È possibile utilizzare questa funzione per modificare il valore originale della cella JSON nell'istruzione UPDATE:

update Product
set Data = JSON_MODIFY(Data, '$.Price', 24.99)
where ProductID = 17;

La funzione JSON_MODIFY aggiornerà o creerà il tasto prezzo (se non esiste). Se il nuovo valore è NULL, la chiave verrà rimossa. La funzione JSON_MODIFY tratterà il nuovo valore come stringa (sfugge ai caratteri speciali, lo avvolge con virgolette doppie per creare una stringa JSON corretta). Se il tuo nuovo valore è frammento JSON, dovresti eseguirlo con la funzione JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, '$.tags', JSON_QUERY('["promo","new"]'))
where ProductID = 17;

La funzione JSON_QUERY senza secondo parametro si comporta come un "cast su JSON". Poiché il risultato di JSON_QUERY è un frammento JSON valido (oggetto o array), JSON_MODIFY non sfuggirà a questo valore quando modifica l'input JSON.

Aggiungi un nuovo valore nell'array JSON

La funzione JSON_MODIFY può essere utilizzata per aggiungere un nuovo valore ad un array all'interno di JSON:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', "sales")
where ProductID = 17;

Il nuovo valore verrà aggiunto alla fine dell'array o verrà creato un nuovo array con valore ["vendite"]. La funzione JSON_MODIFY tratterà il nuovo valore come stringa (sfugge ai caratteri speciali, lo avvolge con virgolette doppie per creare una stringa JSON corretta). Se il tuo nuovo valore è frammento JSON, dovresti eseguirlo con la funzione JSON_QUERY:

update Product
set Data = JSON_MODIFY(Data, 'append $.tags', JSON_QUERY('{"type":"new"}'))
where ProductID = 17;

La funzione JSON_QUERY senza secondo parametro si comporta come un "cast su JSON". Poiché il risultato di JSON_QUERY è un frammento JSON valido (oggetto o array), JSON_MODIFY non sfuggirà a questo valore quando modifica l'input JSON.

Tavolo JOIN con collezione JSON interna

Se si dispone di una "tabella figlio" formattata come raccolta JSON e memorizzata in-row come colonna JSON, è possibile decomprimere questa raccolta, trasformarla in tabella e aggiungerla alla riga padre. Invece dell'operatore JOIN standard, dovresti usare CROSS APPLY. In questo esempio, le parti del prodotto sono formattate come raccolta di oggetti JSON e memorizzate nella colonna Dati:

select ProductID, Name, Size, Price, Quantity, PartName, Code
from Product
    CROSS APPLY OPENJSON(Data, '$.Parts') WITH (PartName varchar(20), Code varchar(5))

Il risultato della query è equivalente al join tra le tabelle Prodotto e Parte.

Ricerca di righe che contengono valore nell'array JSON

In questo esempio, l'array di tag può contenere varie parole chiave come ["promo", "vendite"], quindi è possibile aprire questo array e filtrare i valori:

select ProductID, Name, Color, Size, Price, Quantity
from Product
    CROSS APPLY OPENJSON(Data, '$.Tags') 
where value = 'sales'

OPENJSON aprirà la raccolta interna dei tag e la restituirà come tabella. Quindi possiamo filtrare i risultati per un certo valore nella tabella.