JSON-dokumentumok tárolása

Vonatkozik a következőkre: SQL Server 2016 (13.x) és későbbi verziók Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Az SQL Database Engine natív JSON-függvényeket biztosít, amelyek lehetővé teszik JSON-dokumentumok szabványos SQL-nyelv használatával történő elemzését. A JSON-dokumentumokat az SQL Database Engine-ben tárolhatja, és JSON-adatokat kérdezhet le, mint egy NoSQL-adatbázisban. Ez a cikk a JSON-dokumentumok tárolásának lehetőségeit ismerteti.

Formátum JSON tárhely

Az első tárolási tervezési döntés a JSON-dokumentumok táblákban való tárolása. Két lehetőség áll rendelkezésre:

• LOB storage – A JSON-dokumentumok as-is tárolhatók oszlopokban, json vagy nvarchar. Ez a legjobb módja az adatok gyors feltöltésének és feldolgozásának, mivel a betöltési sebesség megegyezik a szöveges oszlopok betöltési sebességével. Ez a módszer további teljesítménybeli büntetést vonhat maga után a lekérdezési/elemzési időszakban, ha a JSON-értékek indexelése nem történik meg, mert a nyers JSON-dokumentumokat elemezni kell a lekérdezések futtatása közben.
• Relációs tárterület – A JSON-dokumentumok elemezhetők, miközben OPENJSON, JSON_VALUE vagy JSON_QUERY függvényekkel szúrják be őket a táblába. A bemeneti JSON-dokumentumok töredékeit a JSON-alelemeket tartalmazó oszlopokban lehet tárolni, amelyek adattípusa json vagy nvarchar. Ez a megközelítés növeli a betöltési időt, mert a JSON-elemzés a terhelés során történik; a lekérdezések azonban megfelelnek a klasszikus lekérdezések relációs adatokon végzett teljesítményének.
• Az SQL Serveren jelenleg a JSON nem beépített adattípus.

Megjegyzés:

A JSON-adattípus:

• általánosan elérhető az Azure SQL Database és az Azure SQL Managed Instance az SQL Server 2025 vagy az Always-up-to-date frissítési szabályzattal.
• előzetes verzióban érhető el az SQL Server 2025 (17.x) és az SQL Database a Fabricben.

Klasszikus táblák

A JSON-dokumentumok SQL Serverben vagy Azure SQL Database-ben való tárolásának legegyszerűbb módja egy kétoszlopos tábla létrehozása, amely tartalmazza a dokumentum azonosítóját és a dokumentum tartalmát. Például:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

Vagy, ahol támogatott:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

Ez a struktúra megegyezik a klasszikus dokumentum-adatbázisokban található gyűjteményekkel. Az elsődleges kulcs _id egy automatikusan növekvő érték, amely minden dokumentum egyedi azonosítóját biztosítja, és lehetővé teszi a gyors kereséseket. Ez a struktúra jó választás a klasszikus NoSQL-forgatókönyvekhez, ahol azonosító alapján szeretne lekérni egy dokumentumot, vagy azonosító alapján frissíteni szeretné a tárolt dokumentumot.

• Használja a natív json adattípust, ahol elérhető a JSON-dokumentumok tárolásához.
• A nvarchar(max) adattípussal legfeljebb 2 GB méretű JSON-dokumentumokat tárolhat. Ha biztos abban, hogy a JSON-dokumentumok nem nagyobbak 8 KB-nál, javasoljuk, hogy teljesítménybeli okokból nvarchar(4000) használjon nvarchar(max) helyett.

Az előző példában létrehozott mintatábla feltételezi, hogy az érvényes JSON-dokumentumok a log oszlopban vannak tárolva. Ha biztos szeretne lenni abban, hogy az érvényes JSON a log oszlopba van mentve, hozzáadhat egy CHECK korlátozást az oszlophoz. Például:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

Minden alkalommal, amikor valaki beszúr vagy frissít egy dokumentumot a táblában, ez a korlátozás ellenőrzi, hogy a JSON-dokumentum megfelelően van-e formázva. A korlátozás nélkül a táblázat beszúrásokra van optimalizálva, mert minden JSON-dokumentum közvetlenül az oszlophoz lesz hozzáadva feldolgozás nélkül.

Amikor A JSON-dokumentumokat a táblában tárolja, szabványos Transact-SQL nyelv használatával kérdezheti le a dokumentumokat. Például:

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

Nagy előnye, hogy bármilyen T-SQL-függvényt és lekérdezési záradékot használhat JSON-dokumentumok lekérdezéséhez. Az SQL Server és az SQL Database nem vezet be korlátozásokat a JSON-dokumentumok elemzéséhez használható lekérdezésekben. A JSON-dokumentumokból kinyerheti az értékeket a JSON_VALUE függvénnyel, és bármely más értékhez hasonlóan használhatja azt a lekérdezésben.

A gazdag T-SQL-lekérdezési szintaxis használatának képessége az SQL Server, az SQL-adatbázisok és a klasszikus NoSQL-adatbázisok közötti fő különbség – Transact-SQL valószínűleg rendelkezik az összes szükséges függvénnyel JSON-adatok feldolgozásához.

Indexes

Ha kiderül, hogy a lekérdezések gyakran keresnek dokumentumokat valamilyen tulajdonság (például egy JSON-dokumentum severity tulajdonsága) alapján, a lekérdezések felgyorsításához hozzáadhat egy nem klaszteres soros tároló indexet a tulajdonsághoz.

Létrehozhat egy számított oszlopot, amely a megadott elérési úton (azaz az elérési úton $.severity) lévő JSON-oszlopok JSON-értékeit teszi elérhetővé, és szabványos indexet hozhat létre ezen a számított oszlopon. Például:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

A példában használt számított oszlop egy nem tartós vagy virtuális oszlop, amely nem ad hozzá további helyet a táblához. Az index ix_severity használja a lekérdezések teljesítményének javítására, például az alábbi példában:

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

Ennek az indexnek az egyik fontos jellemzője, hogy rendezésérzékeny. Ha az eredeti nvarchar oszlop COLLATION tulajdonsággal rendelkezik (például a kis- és nagybetűk érzékenysége vagy a japán nyelv), az index a nyelvi szabályok vagy a nvarchar oszlophoz társított kis- és nagybetűk érzékenységi szabályai szerint van rendszerezve. Ez a rendezési tudatosság fontos funkció lehet, ha olyan alkalmazásokat fejleszt globális piacokra, amelyeknek egyéni nyelvi szabályokat kell használniuk JSON-dokumentumok feldolgozásakor.

Nagyméretű táblák & oszlop-tároló formátumban

Ha nagy számú JSON-dokumentumra számít a gyűjteményben, javasoljuk, hogy adjon hozzá egy fürtözött oszlopcentrikus indexet a gyűjteményhez, ahogyan az a következő példában látható:

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
);

A fürtözött oszlopcentrikus index magas adattömörítést (akár 25x) biztosít, amely jelentősen csökkentheti a tárterületre vonatkozó követelményeket, csökkentheti a tárolási költségeket, és növelheti a számítási feladat I/O-teljesítményét. Emellett a fürtözött oszlopcentrikus indexek a JSON-dokumentumok táblavizsgálatára és elemzésére vannak optimalizálva, így ez az indextípus lehet a legjobb választás a naplóelemzéshez.

Az előző példa egy sorszámozási objektummal rendel értékeket a _id oszlophoz. Az azonosító oszlopban a sorozatok és az identitások is érvényesek.

Dokumentumok gyakori módosítása & memóriaoptimalizált táblákban

Ha nagy számú frissítési, beszúrási és törlési műveletet vár a gyűjteményekben, a JSON-dokumentumokat memóriaoptimalizált táblákban tárolhatja. A memóriaoptimalizált JSON-gyűjtemények mindig memóriában tartják az adatokat, így nincs szükség a tárolási I/O-költségekre. Emellett a memóriaoptimalizált JSON-gyűjtemények teljesen zárolásmentesek , vagyis a dokumentumokon végzett műveletek semmilyen más műveletet nem tiltanak le.

A klasszikus gyűjteményeket csak memóriaoptimalizált gyűjteménysé kell konvertálnia, ha a tábladefiníció után megadja a WITH (MEMORY_OPTIMIZED=ON) beállítást, ahogy az az alábbi példában is látható. Ezután a JSON-gyűjtemény memóriaoptimalizált verziójával rendelkezik.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] nvarchar(max)
) WITH (MEMORY_OPTIMIZED=ON)

A dokumentumok gyakori módosításához a legjobb megoldás a memóriaoptimalizált táblázat. Ha memóriaoptimalizált táblákat fontolgat, vegye figyelembe a teljesítményt is. Ha lehetséges, használja az nvarchar(4000) adattípust a memóriaoptimalizált gyűjteményekben lévő JSON-dokumentumokhoz nvarchar(max) helyett, mert ez jelentősen javíthatja a teljesítményt. A json adattípus memóriaoptimalizált táblákkal nem támogatott.

A klasszikus táblákhoz hasonlóan a memóriaoptimalizált táblákban feltárt mezők indexeit is hozzáadhatja számított oszlopok használatával. Például:

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)

A teljesítmény maximalizálása érdekében állítsa a JSON-értéket a lehető legkisebb típusra, amely a tulajdonság értékének tárolására használható. Az előző példában tinyint használunk.

A Natív fordítás előnyeit kihasználva olyan SQL-lekérdezéseket is elhelyezhet, amelyek a tárolt eljárásokban frissítik a JSON-dokumentumokat. Például:

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

Ez a natívan lefordított eljárás leküldi a lekérdezést, és .DLL lekérdezést futtató kódot hoz létre. A natívan lefordított eljárás az adatok lekérdezésének és frissítésének gyorsabb módszere.

Conclusion

Az SQL Server és az SQL Database natív JSON-függvényei lehetővé teszik JSON-dokumentumok feldolgozását, ugyanúgy, mint a NoSQL-adatbázisokban. Minden adatbázisnak – relációs vagy NoSQL - van néhány előnye és hátránya a JSON-adatfeldolgozáshoz. A JSON-dokumentumok SQL Serveren vagy SQL Database-ben való tárolásának fő előnye a teljes SQL-nyelv támogatása. A gazdag Transact-SQL nyelv használatával feldolgozhatja az adatokat, és különféle tárolási lehetőségeket konfigurálhat, a magas tömörítési és gyors elemzési oszlopcentrikus indexektől kezdve a zárolásmentes feldolgozáshoz használható memóriaoptimalizált táblákig. Ugyanakkor a kiforrott biztonsági és nemzetköziesítési funkciók előnyeit is élvezheti, amelyeket egyszerűen felhasználhat a NoSQL-forgatókönyvben. A cikkben ismertetett okok kiváló érvek arra, hogy érdemes megfontolni A JSON-dokumentumok SQL Serverben vagy SQL Database-ben való tárolását.

További információ a JSON-ról az SQL Database Engine-ben

A beépített JSON-támogatás vizuális bemutatásához tekintse meg az alábbi videókat:

• A JSON a NoSQL és a relációs világok közötti híd

Kapcsolódó tartalom

• JSON-adatok AZ SQL Server
• JSON-adattípus