Administrer JSON-kolonner og indekser

  • 8 minutter

Relationelle databaser fungerer bedst, når hver række i en tabel har de samme kolonner. Du definerer strukturen én gang, og hver post følger den. Dette design fungerer godt til data som kunder, ordrer eller fakturaer, hvor felterne er forudsigelige. Men nogle data varierer fra post til record. De attributter, du skal gemme, afhænger af typen af objekt, kilden til dataene eller de valg, brugerne har truffet. Traditionelt tabeldesign tvinger dig til enten at oprette mange kolonner, der er tomme for de fleste rækker, eller opdele data på tværs af mange tabeller. JSON-kolonner tilbyder en anden mulighed: gem de variable dele som JSON, mens de forudsigelige dele holdes i almindelige kolonner.

For eksempel har en e-handelskatalog fælles felter som produktnavn, pris og kategori, der gælder for alle varer. Men en skjorte skal have størrelse og farve, en bærbar computer skal have processorhastighed og skærmstørrelse, og en bog skal have forfatter- og andre egenskaber. Med JSON gemmer du de almindelige felter som kolonner og placerer de kategorispecifikke attributter i en JSON-kolonne. Du kan tilføje nye produkttyper uden at ændre tabelstrukturen.

Forstå hvornår du skal bruge JSON-kolonner

JSON-kolonner lader dig forespørge og indeksere semistrukturerede data ved hjælp af velkendt SQL-syntaks. Du behøver ikke en separat NoSQL-database til at håndtere fleksible data. Overvej JSON i disse scenarier:

  • Brugerpræferencer - Indstillinger som tema, sprog og notifikationsmuligheder varierer fra bruger til bruger og ændrer sig, efterhånden som du tilføjer funktioner.
  • API-svar - Data fra eksterne tjenester har indlejrede strukturer, som kan ændres, når udbyderen opdaterer deres API.
  • Revisionslogfiler – Poster, der fanger før- og efter-tilstande, skal tilpasses, efterhånden som dine tabelskemaer udvikler sig.
  • Multi-tenant applikationer - Forskellige kunder kræver forskellige brugerdefinerede felter.
  • Fleksible metadata - Tags, labels og egenskaber, der varierer fra post til post og ikke passer ind i et fast skema.

Opret og forespørg JSON-kolonner

SQL Server 2025 introducerer en indbygget json-datatype, der gemmer JSON-dokumenter i et binært format optimeret til forespørgsler og manipulation. Den native type giver mere effektive læsninger (dokumentet er allerede parset), mere effektive skrivninger (opdateringer kan ændre individuelle værdier uden at omskrive hele dokumentet) og bedre lagringskomprimering sammenlignet med at lagre JSON som NVARCHAR(MAX).

For tidligere versioner af SQL Server gemmer du JSON i en NVARCHAR(MAX) kolonne.

For at læse værdier fra JSON bruger du JSON-funktioner til JSON_VALUE at udtrække en enkelt værdi eller JSON_QUERY returnere et objekt eller array. Hvis du ofte forespørger en JSON-egenskab, kan du oprette et indeks på en beregnet kolonne, der udtrækker den egenskab.

Følgende eksempel opretter en tabel med en JSON-kolonne, indsætter dokumenter, forespørger specifikke egenskaber, opdaterer værdier og opretter et indeks på et ofte tilgået felt:

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

Dette eksempel opretter en tabel med en JSON kolonne, der gemmer brugerens konfigurationsindstillinger. Sætningerne INSERT tilføjer JSON-dokumenter som strengliteraler. For at læse specifikke værdier JSON_VALUE udtrækker den skalarværdier som tema og sprog, mens JSON_QUERY hele JSON-objektet returneres. Metoden .modify() (som i øjeblikket er i forhåndsvisning) opdaterer en enkelt egenskab uden at omskrive hele dokumentet. Da json typen ikke kan bruges som en indeksnøglekolonne, opretter eksemplet en beregnet kolonne, der udtrækker temaværdien, og indekserer derefter den beregnede kolonne.

Kombiner relationel og JSON-struktur

JSON-kolonner fungerer bedst til data, der varierer fra post til post. Hvis hver række har de samme felter med konsistente datatyper, passer almindelige kolonner bedre. Du får validering af native datatyper, enklere forespørgsler uden JSON-stisyntaks og direkte indeksering på kolonner. Brug JSON til de dele af dine data, der kræver fleksibilitet, og hold de forudsigelige dele i typede kolonner.

Du kan kombinere relationel struktur med JSON-fleksibilitet for produkter, der kræver variabel metadata. Her er et eksempel:

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;

Overvej JSON-designprincipper

Anvend disse principper, når du implementerer JSON-kolonner:

  • Brug JSON til semistrukturerede data - Gem fleksible datastrukturer, der varierer fra post til post, ikke data med konsistente skemaer.
  • Indeks ofte forespurgt stier - Opret beregnede kolonner med indekser på JSON-egenskaber, som du ofte forespørger.
  • Valider nødvendige egenskaber - Brug CHECK begrænsninger med JSON_PATH_EXISTS for at sikre, at de nødvendige felter er til stede.
  • Balance fleksibilitet med struktur – Hold forudsigelige data i almindelige kolonner og brug kun JSON til de variable dele.

JSON-kolonner giver skemafleksibilitet for variabeldata, samtidig med at de bevarer SQL-forespørgselsmuligheder, men bør supplere snarere end erstatte relationel design for strukturerede data.