Delen via

JSON-gegevens parseren en transformeren met OPENJSON

  • Artikel

van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (alleen serverloze SQL-pool)SQL Analytics-eindpunt in Microsoft FabricWarehouse in Microsoft Fabric

De functie OPENJSON rijset converteert JSON-tekst naar een set rijen en kolommen. Nadat u een JSON-verzameling hebt getransformeerd in een rijset met OPENJSON, kunt u elke SQL-query uitvoeren op de geretourneerde gegevens of deze invoegen in een SQL Server-tabel. Zie JSON-gegevens in SQL Servervoor meer informatie over het werken met JSON-gegevens in de SQL Server-database-engine.

De functie OPENJSON gebruikt één JSON-object of een verzameling JSON-objecten en transformeert deze in een of meer rijen. Standaard retourneert de OPENJSON functie de volgende gegevens:

  • Vanuit een JSON-object retourneert de functie alle sleutel-/waardeparen die op het eerste niveau worden gevonden.
  • Vanuit een JSON-matrix retourneert de functie alle elementen van de matrix met hun indexen.

U kunt een optionele WITH component toevoegen om een schema op te geven dat expliciet de structuur van de uitvoer definieert.

OPENJSON met de standaarduitvoer

Wanneer u de functie OPENJSON gebruikt zonder een expliciet schema op te geven voor de resultaten, dat wil gezegd, zonder een WITH component na OPENJSON, retourneert de functie een tabel met de volgende drie kolommen:

  1. De name van de eigenschap in het invoerobject (of de index van het element in de invoermatrix).
  2. De value van de eigenschap of het element van een reeks.
  3. De type (bijvoorbeeld tekenreeks, getal, booleaanse waarde, matrix of object).

OPENJSON retourneert elke eigenschap van het JSON-object of elk element van de matrix als een afzonderlijke rij.

In het volgende voorbeeld wordt OPENJSON gebruikt met het standaardschema, dat wil gezegd, zonder de optionele WITH-component, en wordt één rij geretourneerd voor elke eigenschap van het JSON-object.

DECLARE @json NVARCHAR(MAX);

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

SELECT *
FROM OPENJSON(@json);

Dit is de resultaatset.

sleutel waarde type
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Zie OPENJSON gebruiken met het standaardschemavoor meer informatie en voorbeelden.

Zie OPENJSON-voor syntaxis en gebruik.

OPENJSON-uitvoer met een expliciete structuur

Wanneer u een schema voor de resultaten opgeeft met behulp van de WITH component van de OPENJSON functie, retourneert de functie een tabel met alleen de kolommen die u in de WITH component definieert. In de optionele WITH-component geeft u een set uitvoerkolommen, de bijbehorende typen en de paden van de JSON-broneigenschappen voor elke uitvoerwaarde op. OPENJSON doorloopt de array van JSON-objecten, leest de waarde op het gespecificeerde pad voor elke kolom, en converteert de waarde naar het opgegeven type.

In het volgende voorbeeld wordt OPENJSON gebruikt met een schema voor de uitvoer dat u expliciet in de WITH-clausule opgeeft.

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

Dit is de resultaatset.

Getal Datum Klant Hoeveelheid
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Met deze functie worden de elementen van een JSON-matrix geretourneerd en opgemaakt.

  • Voor elk element in de JSON-matrix genereert OPENJSON een nieuwe rij in de uitvoertabel. De twee elementen in de JSON-matrix worden geconverteerd naar twee rijen in de geretourneerde tabel.

  • Voor elke kolom, opgegeven met behulp van de syntaxis van de colName type json_path, converteert OPENJSON de waarde die in elk matrixelement op het opgegeven pad naar het opgegeven type is gevonden. In dit voorbeeld worden waarden voor de kolom Date genomen uit elk element op het pad $.Order.Date en geconverteerd naar datetime-waarden.

Zie OPENJSON gebruiken met een expliciet schema (SQL Server)voor meer informatie en voorbeelden.

Zie OPENJSON-voor syntaxis en gebruik.

OPENJSON vereist compatibiliteitsniveau 130

De functie OPENJSON is alleen beschikbaar onder compatibiliteitsniveau 130 en hoger. Als uw databasecompatibiliteitsniveau lager is dan 130, kan SQL Server de OPENJSON functie niet vinden en uitvoeren. Andere ingebouwde JSON-functies zijn beschikbaar op alle compatibiliteitsniveaus.

U kunt het compatibiliteitsniveau controleren in de sys.databases weergave of in database-eigenschappen en het compatibiliteitsniveau van een database wijzigen met behulp van de volgende opdracht:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Verwante inhoud