Dela via

JSON-data i SQL Server

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL-databas i Microsoft Fabric

Den här artikeln innehåller en översikt över det textbaserade dataformatet JSON i SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics och SQL-databasen i Microsoft Fabric.

Anmärkning

Översikt

JSON är ett populärt textdataformat som används för att utbyta data i moderna webb- och mobilprogram. JSON används också för att lagra ostrukturerade data i loggfiler eller NoSQL-databaser som Microsoft Azure Cosmos DB. Många REST-webbtjänster returnerar resultat som är formaterade som JSON-text eller accepterar data som är formaterade som JSON. De flesta Azure-tjänster, till exempel Azure Search, Azure Storage och Azure Cosmos DB, har till exempel REST-slutpunkter som returnerar eller använder JSON. JSON är också huvudformatet för att utbyta data mellan webbsidor och webbservrar med hjälp av AJAX-anrop.

Med JSON-funktioner, som först introducerades i SQL Server 2016 (13.x), kan du kombinera NoSQL och relationsbegrepp i samma databas. Du kan kombinera klassiska relationskolumner med kolumner som innehåller dokument som är formaterade som JSON-text i samma tabell, parsa och importera JSON-dokument i relationsstrukturer eller formatera relationsdata till JSON-text.

Följande är ett exempel på JSON-text:

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

Med hjälp av inbyggda funktioner och operatorer i SQL Server kan du göra följande med JSON-text:

  • Parsa JSON-text och läsa eller ändra värden.
  • Omvandla matriser med JSON-objekt till tabellformat.
  • Kör valfri Transact-SQL fråga på de konverterade JSON-objekten.
  • Formatera resultatet av Transact-SQL frågor i JSON-format.

Diagram som visar översikten över inbyggt JSON-stöd.

SQL Server 2025-ändringar

Förhandsversionen av SQL Server 2025 (17.x) introducerar följande JSON-förbättringar, som för närvarande är i förhandsversion:

Viktiga JSON-funktioner

I nästa avsnitt beskrivs de viktigaste funktionerna som SQL Server tillhandahåller med sitt inbyggda JSON-stöd.

JSON-datatyp

Den nya json-datatypen som lagrar JSON-dokument i ett internt binärt format som ger följande fördelar jämfört med att lagra JSON-data i varchar/nvarchar:

  • Mer effektiva läsningar eftersom dokumentet redan är parsat
  • Effektivare skrivningar eftersom frågan kan uppdatera enskilda värden utan att komma åt hela dokumentet
  • Effektivare lagring, optimerad för komprimering
  • Ingen ändring i kompatibiliteten med befintlig kod

Anmärkning

JSON-datatypen:

  • är allmänt tillgängligt för Azure SQL Database och Azure SQL Managed Instance som konfigurerats med uppdateringsprincipen Always-up-to-date.
  • är i förhandsversion för SQL Server 2025 (17.x) Preview.

Att använda JSON-samma funktioner som beskrivs i den här artikeln är fortfarande det mest effektiva sättet att fråga json-datatypen . Mer information om den interna json-datatypen finns i JSON-datatypen.

Extrahera värden från JSON-text och använd dem i frågor

Om du har JSON-text som lagras i databastabeller kan du läsa eller ändra värden i JSON-texten med hjälp av följande inbyggda funktioner:

Exempel

I följande exempel använder frågan både relations- och JSON-data (lagras i en kolumn med namnet jsonCol) från en tabell med namnet People:

SELECT Name,
    Surname,
    JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
    JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')
        + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
    JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
    AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
    AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Program och verktyg ser ingen skillnad mellan värdena från skalära tabellkolumner och de värden som hämtas från JSON-kolumner. Du kan använda värden från JSON-text i valfri del av en Transact-SQL fråga (inklusive WHERE-, ORDER BY- eller GROUP BY-satser, fönsteraggregeringar och så vidare). JSON-funktioner använder JavaScript-liknande syntax för att referera till värden i JSON-text.

För mer information, se Validate, Query, and Change JSON Data with Built-in Functions (SQL Server), JSON_VALUE (Transact-SQL) och JSON_QUERY (Transact-SQL).

Ändra JSON-värden

Om du måste ändra delar av JSON-text kan du använda funktionen JSON_MODIFY (Transact-SQL) för att uppdatera värdet för en egenskap i en JSON-sträng och returnera den uppdaterade JSON-strängen. I följande exempel uppdateras värdet för en egenskap i en variabel som innehåller JSON:

DECLARE @json NVARCHAR(MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT modifiedJson = @json;

Här är resultatet.

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

Konvertera JSON-samlingar till en raduppsättning

Du behöver inget anpassat frågespråk för att köra frågor mot JSON i SQL Server. Om du vill köra frågor mot JSON-data kan du använda standard-T-SQL. Om du måste skapa en fråga eller rapport om JSON-data kan du enkelt konvertera JSON-data till rader och kolumner genom att anropa OPENJSON funktionen rowset. Mer information finns i Parsa och transformera JSON-data med OPENJSON.

I följande exempel anropas OPENJSON och transformeras matrisen med objekt som lagras i variabeln @json till en raduppsättning som kan efterfrågas med en standard-Transact-SQL-instruktion SELECT :

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

Här är resultatet.

ID-nummer förnamn efternamn ålder födelsedatum
2 John Smed 25
5 Jane Smed 2005-11-04T12:00:00

OPENJSON omvandlar matrisen med JSON-objekt till en tabell där varje objekt representeras som en rad och nyckel/värde-par returneras som celler. Utdata följer följande regler:

  • OPENJSON konverterar JSON-värden till de typer som anges i WITH -satsen.
  • OPENJSON kan hantera både flata nyckel/värde-par och kapslade, hierarkiskt ordnade objekt.
  • Du behöver inte returnera alla fält som finns i JSON-texten.
  • Om JSON-värden inte finns OPENJSON returnerar du NULL värden.
  • Du kan också ange en sökväg efter typspecifikationen för att referera till en kapslad egenskap eller referera till en egenskap med ett annat namn.
  • Det valfria strict prefixet i sökvägen anger att värden för de angivna egenskaperna måste finnas i JSON-texten.

Mer information finns i Parsa och transformera JSON-data med OPENJSON och OPENJSON (Transact-SQL).

JSON-dokument kan ha underelement och hierarkiska data som inte kan mappas direkt till standardrelationskolumnerna. I det här fallet kan du platta ut JSON-hierarkin genom att ansluta en överordnad entitet till undermatriser.

I följande exempel har det andra objektet i matrisen undermatris som representerar personkunskaper. Varje underobjekt kan parsas med hjälp av ytterligare OPENJSON funktionsanrop:

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');

Matrisen skills returneras i det första OPENJSON som det ursprungliga JSON-textfragmentet och skickas till en annan OPENJSON funktion med operatorn APPLY . Den andra OPENJSON funktionen parsar JSON-matrisen och returnerar strängvärden som en kolumnraduppsättning som ska kopplas till resultatet av den första OPENJSON.

Här är resultatet.

ID-nummer förnamn efternamn ålder födelsedatum kompetens
2 John Smed 25
5 Jane Smed 2005-11-04T12:00:00 SQL
5 Jane Smed 2005-11-04T12:00:00 C#
5 Jane Smed 2005-11-04T12:00:00 Blått

OUTER APPLY OPENJSON ansluter entitet på första nivån med en undermatris och returnerar en utplattad resultatuppsättning. På grund av JOIN upprepas den andra raden för varje färdighet.

Konvertera SQL Server-data till JSON eller exportera JSON

Anmärkning

Det går inte att konvertera Azure Synapse Analytics-data till JSON eller exportera JSON.

Formatera SQL Server-data eller resultat av SQL-frågor som JSON genom att lägga till FOR JSON-satsen till en SELECT-instruktion. Använd FOR JSON för att delegera formatering av JSON-utdata från dina klientprogram till SQL Server. Mer information finns i Formatera frågeresultat som JSON med FOR JSON.

I följande exempel används PATH-läge med FOR JSON -satsen:

SELECT id,
    firstName AS "info.name",
    lastName AS "info.surname",
    age,
    dateOfBirth AS dob
FROM People
FOR JSON PATH;

Satsen FOR JSON formaterar SQL-resultat som JSON-text som kan tillhandahållas till alla appar som förstår JSON. Alternativet PATH använder punktavgränsade alias i SELECT-satsen för att kapsla objekt i frågeresultatet.

Här är resultatet.

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

Mer information finns i Formatera frågeresultat som JSON med FOR JSON och FOR Clause (Transact-SQL).

JSON-data från aggregeringar

JSON-mängdfunktioner möjliggör konstruktion av JSON-objekt eller matriser baserat på en aggregering från SQL-data.

  • JSON_OBJECTAGG konstruerar ett JSON-objekt från en aggregering av SQL-data eller kolumner.
  • JSON_ARRAYAGG konstruerar en JSON-matris från en aggregering av SQL-data eller kolumner.

Anmärkning

Både json mängdfunktioner JSON_OBJECTAGG och JSON_ARRAYAGG är för närvarande i förhandsversion för Azure SQL Database och Azure SQL Managed Instance (konfigurerade med Always-up-to-date uppdateringsprincip).

Användningsfall för JSON-data i SQL Server

Med JSON-stöd i SQL Server och Azure SQL Database kan du kombinera relationsbegrepp och NoSQL-begrepp. Du kan enkelt transformera relationella till halvstrukturerade data och vice versa. JSON ersätter dock inte befintliga relationsmodeller. Här följer några specifika användningsfall som drar nytta av JSON-stödet i SQL Server och i SQL Database.

Förenkla komplexa datamodeller

Överväg att avnormalisera datamodellen med JSON-fält i stället för flera underordnade tabeller.

Lagra detaljhandels- och e-handelsdata

Lagra information om produkter med ett brett utbud av variabelattribut i en avnormaliserad modell för flexibilitet.

Bearbeta logg- och telemetridata

Läs in, fråga och analysera loggdata som lagras som JSON-filer med all kraften i det Transact-SQL språket.

Lagra halvstrukturerade IoT-data

När du behöver realtidsanalys av IoT-data läser du in inkommande data direkt i databasen i stället för att mellanlagring av dem på en lagringsplats.

Förenkla REST API-utveckling

Omvandla relationsdata från databasen enkelt till det JSON-format som används av REST-API:er som stöder din webbplats.

Kombinera relations- och JSON-data

SQL Server tillhandahåller en hybridmodell för lagring och bearbetning av både relations- och JSON-data med hjälp av standardspråket Transact-SQL. Du kan organisera samlingar av dina JSON-dokument i tabeller, upprätta relationer mellan dem, kombinera starkt typbeskrivna skalärkolumner som lagras i tabeller med flexibla nyckel/värde-par som lagras i JSON-kolumner och fråga både skalära och JSON-värden i en eller flera tabeller med hjälp av fullständig Transact-SQL.

JSON-text lagras i VARCHAR eller NVARCHAR kolumner och indexeras som oformaterad text. Alla SQL Server-funktioner eller komponenter som stöder text stöder JSON, så det finns nästan inga begränsningar för interaktion mellan JSON och andra SQL Server-funktioner. Du kan lagra JSON i minnesintern eller temporala tabeller, tillämpa Row-Level säkerhetspredikat på JSON-text och så vidare.

Här följer några användningsfall som visar hur du kan använda det inbyggda JSON-stödet i SQL Server.

Lagra och indexera JSON-data i SQL Server

JSON är ett textformat så att JSON-dokumenten kan lagras i NVARCHAR kolumner i en SQL Database. Eftersom NVARCHAR typen stöds i alla SQL Server-undersystem kan du placera JSON-dokument i tabeller med grupperade kolumnlagringsindex, minnesoptimerade tabeller eller externa filer som kan läsas med OPENROWSET eller PolyBase.

Mer information om alternativen för att lagra, indexera och optimera JSON-data i SQL Server finns i följande artiklar:

Läsa in JSON-filer till SQL Server

Du kan formatera information som lagras i filer som JSON-standard eller radavgränsad JSON. SQL Server kan importera innehållet i JSON-filer, parsa det med hjälp OPENJSON av funktionerna eller JSON_VALUE och läsa in det i tabeller.

  • Om dina JSON-dokument lagras i lokala filer, på delade nätverksenheter eller på Azure Files-platser som kan nås av SQL Server kan du använda massimport för att läsa in dina JSON-data till SQL Server.

  • Om dina radavgränsade JSON-filer lagras i Azure Blob Storage eller Hadoop-filsystemet kan du använda PolyBase för att läsa in JSON-text, parsa den i Transact-SQL kod och läsa in den i tabeller.

Importera JSON-data till SQL Server-tabeller

Om du måste läsa in JSON-data från en extern tjänst till SQL Server kan du använda OPENJSON för att importera data till SQL Server i stället för att parsa data i programlagret.

I plattformar som stöds använder du den interna json-datatypen i stället för nvarchar(max) för bättre prestanda och effektivare lagring.

DECLARE @jsonVariable NVARCHAR(MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

Du kan ange innehållet i JSON-variabeln av en extern REST-tjänst, skicka den som en parameter från ett JavaScript-ramverk på klientsidan eller läsa in den från externa filer. Du kan enkelt infoga, uppdatera eller sammanfoga resultat från JSON-text i en SQL Server-tabell.

Analysera JSON-data med SQL-frågor

Om du måste filtrera eller aggregera JSON-data i rapporteringssyfte kan du använda OPENJSON för att transformera JSON till relationsformat. Du kan sedan använda standardfunktioner för Transact-SQL och inbyggda funktioner för att förbereda rapporterna.

SELECT Tab.Id,
    SalesOrderJsonData.Customer,
    SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON(Tab.json, N'$.Orders.OrdersArray') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
    Tab.DateModified;

Du kan använda både standardtabellkolumner och värden från JSON-text i samma fråga. Du kan lägga till index i JSON_VALUE(Tab.json, '$.Status') uttrycket för att förbättra frågans prestanda. Mer information finns i Index-JSON-data.

Returnera data från en SQL Server-tabell formaterad som JSON

Om du har en webbtjänst som tar data från databaslagret och returnerar dem i JSON-format, eller om du har JavaScript-ramverk eller -bibliotek som accepterar data som är formaterade som JSON, kan du formatera JSON-utdata direkt i en SQL-fråga. I stället för att skriva kod eller inkludera ett bibliotek för att konvertera tabellfrågaresultat och sedan serialisera objekt till JSON-format kan du använda FOR JSON för att delegera JSON-formateringen till SQL Server.

Du kanske till exempel vill generera JSON-utdata som är kompatibla med OData-specifikationen. Webbtjänsten förväntar sig en begäran och ett svar i följande format:

  • Begäran: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

  • Svar: {"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}

Denna OData-URL representerar en begäran för kolumnerna ProductID och ProductName för produkten med ID 1. Du kan använda FOR JSON för att formatera utdata som förväntat i SQL Server.

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
  ProductID,
  Name as ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;

Utdata från den här frågan är JSON-text som är helt kompatibel med OData-specifikationen. Formatering och undantag hanteras av SQL Server. SQL Server kan också formatera frågeresultat i valfritt format, till exempel OData JSON eller GeoJSON.

Inbyggt JSON-stöd för testkörning med AdventureWorks-exempeldatabasen

Hämta AdventureWorks-exempeldatabasen genom att ladda ned åtminstone databasfilen och exempel- och skriptfilen från GitHub.

När du har återställt exempeldatabasen till en instans av SQL Server extraherar du exempelfilen och öppnar JSON Sample Queries procedures views and indexes.sql sedan filen från JSON-mappen. Kör skripten i den här filen för att formatera om vissa befintliga data som JSON-data, testa exempelfrågor och rapporter över JSON-data, indexera JSON-data och importera och exportera JSON.

Det här kan du göra med skripten som ingår i filen:

  • Avnormalisera det befintliga schemat för att skapa kolumner med JSON-data.

    • Lagra information från SalesReasons, SalesOrderDetails, SalesPerson, Customeroch andra tabeller som innehåller information om försäljningsorder i JSON-kolumner i SalesOrder_json tabellen.

    • Lagra information från EmailAddresses och PersonPhone tabeller i Person_json tabellen som matriser med JSON-objekt.

  • Skapa procedurer och vyer som frågar efter JSON-data.

  • Indexera JSON-data. Skapa index för JSON-egenskaper och fulltextindex.

  • Importera och exportera JSON. Skapa och kör procedurer som exporterar innehållet i tabellerna Person och SalesOrder som JSON-resultat, samt importera och uppdatera tabellerna Person och SalesOrder med JSON-indata.

  • Kör frågeexempel. Kör några frågor som anropar lagrade procedurer och vyer som du skapade i steg 2 och 4.

  • Rensa skript. Kör inte den här delen om du vill behålla de lagrade procedurer och vyer som du skapade i steg 2 och 4.

Relaterat innehåll