Microsoft SQL Server
Frågor med JSON-data

Använda värden från JSON i frågan

Funktionen JSON_VALUE gör att du kan ta data från JSON-texten på den sökväg som anges som det andra argumentet och använda detta värde i valfri fråga:

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

Använda JSON-värden i rapporter

När JSON-värden har extraherats från JSON-text kan du använda dem i en valfri del av frågan. Du kan skapa någon form av rapport om JSON-data med gruppaggregeringar osv:

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

Filtrera bort dålig JSON-text från sökresultat

Om viss JSON-text kanske inte är ordentligt formaterad kan du ta bort dessa poster från frågan med ISJSON-funktionen.

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

Uppdatera värdet i JSON-kolumnen

Funktionen JSON_MODIFY kan användas för att uppdatera värdet på någon väg. Du kan använda den här funktionen för att ändra originalvärdet för JSON-cellen i UPDATE-uttalandet:

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

Funktionen JSON_MODIFY kommer att uppdatera eller skapa prisnyckel (om den inte finns). Om det nya värdet är NULL tas bort nyckeln. Funktionen JSON_MODIFY kommer att behandla nytt värde som sträng (undgå specialtecken, lindra det med dubbla citat för att skapa rätt JSON-sträng). Om ditt nya värde är JSON-fragment, bör du lasta in det med JSON_QUERY-funktionen:

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

JSON_QUERY-funktion utan andra parameter uppträder som en "cast till JSON". Eftersom resultatet av JSON_QUERY är giltigt JSON-fragment (objekt eller array) kommer JSON_MODIFY inte att undgå detta värde när modifierar ingång JSON.

Lägg till nytt värde i JSON-matrisen

JSON_MODIFY-funktionen kan användas för att lägga till nytt värde till en grupp i JSON:

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

Nytt värde läggs till i slutet av arrayen, eller så skapas en ny matris med värde ["försäljning"]. Funktionen JSON_MODIFY kommer att behandla nytt värde som sträng (undgå specialtecken, lindra det med dubbla citat för att skapa rätt JSON-sträng). Om ditt nya värde är JSON-fragment, bör du lasta in det med JSON_QUERY-funktionen:

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

JSON_QUERY-funktion utan andra parameter uppträder som en "cast till JSON". Eftersom resultatet av JSON_QUERY är giltigt JSON-fragment (objekt eller array) kommer JSON_MODIFY inte att undgå detta värde när modifierar ingång JSON.

GÅ MED bord med inre JSON-kollektion

Om du har ett "barntabell" formaterat som JSON-samling och lagras i rad som JSON-kolumn, kan du packa upp denna samling, omvandla den till tabell och gå med den i överordnade rad. Istället för standard JOIN-operatören bör du använda CROSS APPLY. I det här exemplet formateras produktdelar som samling av JSON-objekt i och lagras i kolumnen Data:

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

Resultatet av frågan motsvarar sammankopplingen mellan produkt- och deltabeller.

Hitta rader som innehåller värde i JSON-matrisen

I det här exemplet kan taggarray innehålla olika nyckelord som ["promo", "försäljning"], så vi kan öppna denna matris och filtrera värden:

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

OPENJSON öppnar inre samling av taggar och returnerar den som tabell. Då kan vi filtrera resultat efter ett värde i tabellen.