Lagra JSON-dokument
gäller för:
SQL Server 2016 (13.x) och senare Azure SQL DatabaseAzure SQL Managed Instance
SQL Database Engine innehåller interna JSON-funktioner som gör att du kan parsa JSON-dokument med sql-standardspråket. Du kan lagra JSON-dokument i SQL Server eller SQL Database och köra frågor mot JSON-data som i en NoSQL-databas. I den här artikeln beskrivs alternativen för att lagra JSON-dokument.
JSON-lagringsformat
Det första beslutet för lagringsdesign är hur du lagrar JSON-dokument i tabellerna. Det finns två tillgängliga alternativ:
LOB-lagring: JSON-dokumenten kan lagras as-is i kolumner med datatypen json eller nvarchar. Det här är det bästa sättet för snabb datainläsning och inmatning eftersom inläsningshastigheten matchar inläsningshastigheten för strängkolumner. Den här metoden kan medföra ytterligare prestandaförseningar vid fråge-/analystid om indexering på JSON-värden inte utförs, eftersom JSON-rådokumenten måste parsas medan frågorna körs.
Relationslagring – JSON-dokument kan parsas när de infogas i tabellen med hjälp av funktionerna
OPENJSON,JSON_VALUEellerJSON_QUERY. Fragment från JSON-indatadokumenten kan lagras i kolumner som innehåller JSON-underelement med datatyper json- eller nvarchar. Den här metoden ökar belastningstiden eftersom JSON-parsning utförs under belastningen. Frågor matchar dock prestandan för klassiska frågor på relationsdata.JSON-datatypen finns för närvarande i förhandsversion för Azure SQL Database och Azure SQL Managed Instance (konfigurerad med Always-up-to-date uppdateringsprincip).
JSON är för närvarande inte en inbyggd datatyp i SQL Server.
Klassiska tabeller
Det enklaste sättet att lagra JSON-dokument i SQL Server eller Azure SQL Database är att skapa en tabell med två kolumner som innehåller dokumentets ID och innehållet i dokumentet. Till exempel:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max)
);
Eller, där det stöds:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] json
);
Den här strukturen motsvarar de samlingar som du kan hitta i klassiska dokumentdatabaser. Den primära nyckeln _id är ett automatiskt inkrementellt värde som ger en unik identifierare för varje dokument och möjliggör snabba sökningar. Den här strukturen är ett bra val för de klassiska NoSQL-scenarierna där du vill hämta ett dokument efter ID eller uppdatera ett lagrat dokument med ID.
- Använd den interna json datatyp där det är tillgängligt för att lagra JSON-dokument.
- Med nvarchar(max) datatyp kan du lagra JSON-dokument som är upp till 2 GB stora. Om du är säker på att dina JSON-dokument inte är större än 8 kB rekommenderar vi dock att du använder nvarchar(4000) i stället för nvarchar(max) av prestandaskäl.
Exempeltabellen som skapades i föregående exempel förutsätter att giltiga JSON-dokument lagras i kolumnen log. Om du vill vara säker på att giltig JSON sparas i kolumnen log kan du lägga till en CHECK-begränsning i kolumnen. Till exempel:
ALTER TABLE WebSite.Logs
ADD CONSTRAINT [Log record should be formatted as JSON]
CHECK (ISJSON([log])=1)
Varje gång någon infogar eller uppdaterar ett dokument i tabellen verifierar den här begränsningen att JSON-dokumentet är korrekt formaterat. Utan begränsningen är tabellen optimerad för infogningar, eftersom alla JSON-dokument läggs till direkt i kolumnen utan bearbetning.
När du lagrar dina JSON-dokument i tabellen kan du använda standardspråket Transact-SQL för att köra frågor mot dokumenten. Till exempel:
SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
FROM WebSite.Logs
WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
GROUP BY JSON_VALUE([log], '$.severity')
HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC
Det är en stor fördel att du kan använda alla T-SQL-funktioner och frågesatser för att ställa frågor till JSON-dokument. SQL Server och SQL Database introducerar inga begränsningar i de frågor som du kan använda för att analysera JSON-dokument. Du kan extrahera värden från ett JSON-dokument med funktionen JSON_VALUE och använda det i frågan som andra värden.
Den här möjligheten att använda omfattande T-SQL-frågesyntax är den viktigaste skillnaden mellan SQL Server och SQL Database och klassiska NoSQL-databaser – i Transact-SQL har du förmodligen någon funktion som du behöver för att bearbeta JSON-data.
Indexar
Om du upptäcker att dina förfrågningar ofta söker i dokument efter en viss egenskap (till exempel en severity-egenskap i ett JSON-dokument) kan du lägga till ett icke-klustrat rowstore-index på egenskapen för att snabba upp förfrågningarna.
Du kan skapa en beräknad kolumn som exponerar JSON-värden från JSON-kolumnerna på den angivna sökvägen (dvs. på sökvägen $.severity) och skapa ett standardindex för den här beräknade kolumnen. Till exempel:
create table WebSite.Logs (
[_id] bigint primary key identity,
[log] nvarchar(max),
[severity] AS JSON_VALUE([log], '$.severity'),
index ix_severity (severity)
);
Den beräknade kolumnen som används i det här exemplet är en icke-persistent eller virtuell kolumn som inte lägger till extra utrymme i tabellen. Det används av indexet ix_severity för att förbättra prestandan för frågorna som i följande exempel:
SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'
En viktig egenskap hos detta index är att det är kollationsmedvetet. Om din ursprungliga nvarchar kolumn har en COLLATION egenskap (till exempel skiftlägeskänslighet eller japanskt språk) ordnas indexet enligt språkregler eller skiftlägeskänslighetsregler som är associerade med kolumnen nvarchar. Den här sorteringsmedvetenheten kan vara en viktig funktion om du utvecklar program för globala marknader som behöver använda anpassade språkregler vid bearbetning av JSON-dokument.
Stora tabeller & kolumnlagringsformat
Om du förväntar dig att ha ett stort antal JSON-dokument i samlingen rekommenderar vi att du lägger till ett grupperat columnstore-index i samlingen, som du ser i följande exempel:
create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
[_id] bigint default(next value for WebSite.LogID),
[log] nvarchar(max),
INDEX cci CLUSTERED COLUMNSTORE
);
Ett grupperat columnstore-index ger hög datakomprimering (upp till 25 x) som avsevärt kan minska dina lagringsutrymmeskrav, sänka kostnaden för lagring och öka I/O-prestanda för din arbetsbelastning. Dessutom är grupperade kolumnlagringsindex optimerade för tabellgenomsökningar och analyser i dina JSON-dokument, så den här typen av index kan vara det bästa alternativet för log analytics.
I föregående exempel används ett sekvensobjekt för att tilldela värden till kolumnen _id. Både sekvenser och identiteter är giltiga alternativ för ID-kolumnen.
Dokument som ofta ändras & minnesoptimerade tabeller
Om du förväntar dig ett stort antal åtgärder för uppdatering, infogning och borttagning i dina samlingar kan du lagra dina JSON-dokument i minnesoptimerade tabeller. Minnesoptimerade JSON-samlingar behåller alltid data i minnet, så det finns inga lagrings-I/O-kostnader. Dessutom är minnesoptimerade JSON-samlingar helt låsfria, dvs. åtgärder på dokument blockerar inte någon annan åtgärd.
Det enda du behöver göra för att konvertera en klassisk samling till en minnesoptimerad samling är att ange alternativet WITH (MEMORY_OPTIMIZED=ON) efter tabelldefinitionen, som du ser i följande exempel. Sedan har du en minnesoptimerad version av JSON-samlingen.
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)
En minnesoptimerad tabell är det bästa alternativet för att ändra dokument ofta. När du överväger minnesoptimerade tabeller bör du även tänka på prestanda. Använd nvarchar(4000) datatyp i stället för nvarchar(max) för JSON-dokument i dina minnesoptimerade samlingar, om möjligt, eftersom det kan avsevärt förbättra prestandan. Datatypen json stöds inte med minnesoptimerade tabeller.
Precis som med klassiska tabeller kan du lägga till index på de fält som du exponerar i minnesoptimerade tabeller med hjälp av beräknade kolumner. Till exempel:
CREATE TABLE WebSite.Logs (
[_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
[log] nvarchar(max),
[severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
INDEX ix_severity (severity)
) WITH (MEMORY_OPTIMIZED=ON)
Om du vill maximera prestandan omvandlar du JSON-värdet till den minsta möjliga typ som kan användas för att lagra värdet för egenskapen. I föregående exempel används tinyint.
Du kan också placera SQL-frågor som uppdaterar JSON-dokument i lagrade procedurer för att få nytta av intern kompilering. Till exempel:
CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION
AS BEGIN
ATOMIC WITH (transaction isolation level = snapshot, language = N'English')
UPDATE WebSite.Logs
SET [log] = JSON_MODIFY([log], @Property, @Value)
WHERE _id = @Id;
END
Den här internt kompilerade proceduren tar frågan och skapar .DLL kod som kör frågan. En intern kompilerad procedur är den snabbare metoden för att fråga och uppdatera data.
Slutsats
Med interna JSON-funktioner i SQL Server och SQL Database kan du bearbeta JSON-dokument precis som i NoSQL-databaser. Varje databas – relationell eller NoSQL – har vissa fördelar och nackdelar för JSON-databearbetning. Den största fördelen med att lagra JSON-dokument i SQL Server eller SQL Database är fullständigt stöd för SQL-språk. Du kan använda det omfattande Transact-SQL språket för att bearbeta data och för att konfigurera en mängd olika lagringsalternativ, från kolumnlagringsindex för hög komprimering och snabb analys, till minnesoptimerade tabeller för låsfri bearbetning. Samtidigt får du fördelarna med mogna säkerhets- och internationaliseringsfunktioner som du enkelt kan återanvända i ditt NoSQL-scenario. De orsaker som beskrivs i den här artikeln är utmärkta skäl att överväga att lagra JSON-dokument i SQL Server eller SQL Database.
Läs mer om JSON i SQL Server och Azure SQL Database
En visuell introduktion till det inbyggda JSON-stödet i SQL Server och Azure SQL Database finns i följande videor: