Parsa och transformera JSON-data med OPENJSON

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (endast serverlös SQL-pool)SQL-analysslutpunkt i Microsoft FabricWarehouse i Microsoft FabricSQL Database i Förhandsversion av Microsoft Fabric

Funktionen OPENJSON rowset konverterar JSON-text till en uppsättning rader och kolumner. När du har omvandlat en JSON-samling till en raduppsättning med OPENJSONkan du köra valfri SQL-fråga på returnerade data eller infoga den i en SQL Server-tabell. Mer information om hur du arbetar med JSON-data i SQL Server Database Engine finns i JSON-data i SQL Server.

Funktionen OPENJSON tar ett enda JSON-objekt eller en samling JSON-objekt och omvandlar dem till en eller flera rader. Som standard returnerar funktionen OPENJSON följande data:

• Från ett JSON-objekt returnerar funktionen alla nyckel/värde-par som hittas på den första nivån.
• Från en JSON-matris returnerar funktionen alla element i matrisen med sina index.

Du kan lägga till en valfri WITH-sats för att tillhandahålla ett schema som uttryckligen definierar strukturen för utdata.

OPENJSON med standardutdata

När du använder funktionen OPENJSON utan att ange ett explicit schema för resultatet , det vill sa utan en WITH-sats efter OPENJSON, returnerar funktionen en tabell med följande tre kolumner:

1. Egenskapens name i indataobjektet (eller indexet för elementet i indatamatrisen).
2. Egenskapens eller matriselementets value.
3. type (till exempel sträng, tal, boolesk, matris eller objekt).

OPENJSON returnerar varje egenskap för JSON-objektet, eller varje element i matrisen, som en separat rad.

I följande exempel används OPENJSON med standardschemat – det vill säga utan den valfria WITH-satsen – och returnerar en rad för varje egenskap för JSON-objektet.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Här är resultaten.

Mer information och exempel finns i Använda OPENJSON med standardschemat.

För syntax och användning, se OPENJSON.

OPENJSON-utdata med en explicit struktur

När du anger ett schema för resultatet med hjälp av WITH-satsen för funktionen OPENJSON returnerar funktionen en tabell med endast de kolumner som du definierar i WITH-satsen. I den valfria WITH-satsen anger du en uppsättning utdatakolumner, deras typer och sökvägarna för JSON-källegenskaperna för varje utdatavärde. OPENJSON itererar genom matrisen med JSON-objekt, läser värdet på den angivna sökvägen för varje kolumn och konverterar värdet till den angivna typen.

I följande exempel används OPENJSON med ett schema för utdata som du uttryckligen anger i WITH-satsen.

DECLARE @json NVARCHAR(MAX);

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

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

Här är resultaten.

Den här funktionen returnerar och formaterar elementen i en JSON-matris.

• För varje element i JSON-matrisen genererar OPENJSON en ny rad i utdatatabellen. De två elementen i JSON-matrisen konverteras till två rader i den returnerade tabellen.

• För varje kolumn, som anges med hjälp av colName type json_path syntax, konverterar OPENJSON värdet som finns i varje matriselement på den angivna sökvägen till den angivna typen. I det här exemplet hämtas värden för kolumnen Date från varje element på sökvägen $.Order.Date och konverteras till datetime-värden.

Mer information och exempel finns i Använda OPENJSON med ett explicit schema.

För syntax och användning, se OPENJSON.

OPENJSON kräver kompatibilitetsnivå 130

Funktionen OPENJSON är endast tillgänglig under kompatibilitetsnivå 130 och större. Om din databaskompatibilitetsnivå är lägre än 130kan SQL Server inte hitta och köra funktionen OPENJSON. Andra inbyggda JSON-funktioner är tillgängliga på alla kompatibilitetsnivåer.

Du kan kontrollera kompatibilitetsnivån i sys.databases-vyn eller i databasegenskaperna och ändra kompatibilitetsnivån för en databas med hjälp av följande kommando:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Related content

• JSON som en brygga mellan NoSQL och relationsvärldar
• OPENJSON (Transact-SQL)