Administrer JSON-kolonner og indekser

  • 8 minutter

Relasjonsdatabaser fungerer best når hver rad i en tabell har de samme kolonnene. Du definerer strukturen én gang, og hver post følger den. Dette designet fungerer godt for data som kunder, ordrer eller fakturaer der feltene er forutsigbare. Men noen data varierer fra post til record. Attributtene du må lagre, avhenger av typen gjenstand, kilden til dataene, eller valg som brukerne tar. Tradisjonell tabelldesign tvinger deg til enten å lage mange kolonner som er tomme for de fleste rader, eller dele data på mange tabeller. JSON-kolonner tilbyr et annet alternativ: lagre de variable delene som JSON mens de forutsigbare delene holdes i vanlige kolonner.

For eksempel har en e-handelskatalog felles felt som produktnavn, pris og kategori som gjelder for alle varer. Men en skjorte trenger størrelse og farge, en laptop trenger prosessorhastighet og skjermstørrelse, og en bok trenger forfatter og andre egenskaper. Med JSON lagrer du de vanlige feltene som kolonner og legger de kategorispesifikke attributtene i en JSON-kolonne. Du kan legge til nye produkttyper uten å endre tabellstrukturen.

Forstå når du skal bruke JSON-kolonner

JSON-kolonner lar deg spørre og indeksere semistrukturerte data ved bruk av kjent SQL-syntaks. Du trenger ikke en separat NoSQL-database for å håndtere fleksible data. Vurder JSON for disse scenariene:

  • Brukerpreferanser – Innstillinger som tema, språk og varslingsvalg varierer fra bruker til bruker og endres etter hvert som du legger til funksjoner.
  • API-svar – Data fra eksterne tjenester har nestede strukturer som kan endres når leverandøren oppdaterer sitt API.
  • Revisjonslogger – Poster som fanger før- og etter-tilstander må tilpasses etter hvert som tabellskjemaene dine utvikler seg.
  • Flerleietakerapplikasjoner – Ulike kunder krever forskjellige tilpassede felt.
  • Fleksibel metadata – Tags, etiketter og egenskaper som varierer fra post til post og ikke passer inn i et fast skjema.

Opprett og søk i JSON-kolonner

SQL Server 2025 introduserer en innebygd json-datatype som lagrer JSON-dokumenter i et binært format optimalisert for forespørsler og manipulering. Den native typen gir mer effektive lesinger (dokumentet er allerede analysert), mer effektive skrivinger (oppdateringer kan endre individuelle verdier uten å skrive hele dokumentet på nytt), og bedre lagringskomprimering sammenlignet med å lagre JSON som NVARCHAR(MAX).

For tidligere versjoner av SQL Server lagrer du JSON i en NVARCHAR(MAX) kolonne.

For å lese verdier fra JSON bruker du JSON-funksjoner for JSON_VALUE å hente ut en enkelt verdi eller JSON_QUERY returnere et objekt eller array. Hvis du ofte spør en JSON-egenskap, kan du lage en indeks på en beregnet kolonne som trekker ut den egenskapen.

Følgende eksempel oppretter en tabell med en JSON-kolonne, setter inn dokumenter, spør spesifikke egenskaper, oppdaterer verdier og lager en indeks på et felt som ofte brukes:

-- 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 eksempelet lager en tabell med en JSON kolonne som lagrer brukerens konfigurasjonsinnstillinger. Setningene INSERT legger til JSON-dokumenter som strengliteraler. For å lese spesifikke verdier, JSON_VALUE trekker ut skalarverdier som tema og språk, mens JSON_QUERY returnerer hele JSON-objektet. Metoden .modify() (som for øyeblikket er i forhåndsvisning) oppdaterer én egenskap uten å skrive hele dokumentet på nytt. Siden json typen ikke kan brukes som en indeksnøkkelkolonne, lager eksempelet en beregnet kolonne som trekker ut temaverdien, og deretter indekserer den beregnede kolonnen.

Kombiner relasjons- og JSON-struktur

JSON-kolonner fungerer best for data som varierer fra post til post. Hvis hver rad har de samme feltene med konsistente datatyper, passer vanlige kolonner bedre. Du får validering av native datatyper, enklere spørringer uten JSON-stisyntaks, og direkte indeksering på kolonner. Bruk JSON for de delene av dataene som trenger fleksibilitet, og hold de forutsigbare delene i typede kolonner.

Du kan kombinere relasjonsstruktur med JSON-fleksibilitet for produkter som krever variabelmetadata. 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;

Vurder JSON-designprinsipper

Bruk disse prinsippene når du implementerer JSON-kolonner:

  • Bruk JSON for semistrukturerte data – Lagre fleksible datastrukturer som varierer fra post til post, ikke data med konsistente skjemaer.
  • Indekser ofte forespurte stier – Lag beregnede kolonner med indekser på JSON-egenskaper du ofte spør i.
  • Valider nødvendige egenskaper – Bruk CHECK begrensninger med JSON_PATH_EXISTS for å sikre at nødvendige felt er til stede.
  • Balanser fleksibilitet med struktur – Hold forutsigbare data i vanlige kolonner og bruk kun JSON for de variable delene.

JSON-kolonner gir skjemafleksibilitet for variabeldata samtidig som de opprettholder SQL-spørringsmuligheter, men bør utfylle snarere enn erstatte relasjonsdesign for strukturerte data.