OPENJSON (Transact-SQL)

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL Analytics-eindpunt in Microsoft Fabric,Warehouse in Microsoft Fabric,SQL Database in Microsoft Fabric Preview

De OPENJSON tabelwaardefunctie parseert JSON-tekst en retourneert objecten en eigenschappen uit de JSON-invoer als rijen en kolommen. Met andere woorden, OPENJSON biedt een rijsetweergave over een JSON-document. U kunt expliciet de kolommen in de rijenset en de JSON-eigenschapspaden opgeven die worden gebruikt om de kolommen te vullen. Aangezien OPENJSON een set rijen wordt geretourneerd, kunt u in de OPENJSON component van een Transact-SQL instructie gebruikenFROM, net zoals u elke andere tabel, weergave of tabelwaardefunctie kunt gebruiken.

Hiermee OPENJSON importeert u JSON-gegevens in SQL Server of converteert u JSON-gegevens naar een relationele indeling voor een app of service die JSON niet rechtstreeks kan gebruiken.

Transact-SQL syntaxis-conventies

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

De OPENJSON tabelwaardefunctie parseert de jsonExpression die is opgegeven als het eerste argument en retourneert een of meer rijen met gegevens uit de JSON-objecten in de expressie. jsonExpression kan geneste subobjecten bevatten. Als u een subobject wilt parseren vanuit jsonExpression, kunt u een padparameter opgeven voor het JSON-subobject.

openjson

De tabelwaardefunctie retourneert standaard OPENJSON drie kolommen, die de sleutelnaam, de waarde en het type van elk key:value paar in jsonExpression bevatten. Als alternatief kunt u expliciet het schema opgeven van de resultatenset die OPENJSON wordt geretourneerd door with_clause op te geven.

with_clause

De with_clause bevat een lijst met kolommen met de bijbehorende typen om OPENJSON terug te keren. OPENJSON Komt standaard overeen met sleutels in jsonExpression met de kolomnamen in with_clause (in dit geval impliceert matchen sleutels dat het hoofdlettergevoelig is). Als een kolomnaam niet overeenkomt met een sleutelnaam, kunt u een optionele column_path opgeven. Dit is een JSON-padexpressies die naar een sleutel in de jsonExpression verwijzen.

Arguments

jsonExpression

Is een Unicode-tekenexpressie die JSON-tekst bevat.

OPENJSON doorloopt de elementen van de matrix of de eigenschappen van het object in de JSON-expressie en retourneert één rij voor elk element of elke eigenschap. Het volgende voorbeeld retourneert elke eigenschap van het object dat is opgegeven als jsonExpression:

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Results:

• De DoublePrecisionFloatingPoint_value voldoet aan IEEE-754.

path

Is een optionele JSON-padexpressie die verwijst naar een object of een matrix in jsonExpression. OPENJSON zoekt naar de JSON-tekst op de opgegeven positie en parseert alleen het fragment waarnaar wordt verwezen. Zie JSON-padexpressies voor meer informatie.

U kunt een variabele opgeven als de waarde van het pad. (Dit wordt niet ondersteund in SQL Server 2016 (13.x) en eerdere versies.)

In het volgende voorbeeld wordt een genest object geretourneerd door het pad op te geven:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Results

Wanneer OPENJSON een JSON-matrix wordt geparseerd, retourneert de functie de indexen van de elementen in de JSON-tekst als sleutels.

De vergelijking die wordt gebruikt om padstappen te vergelijken met de eigenschappen van de JSON-expressie, is hoofdlettergevoelig en sorteringsbewust (dat wil gezegd een BIN2-vergelijking).

Identiteit van matrixelement

de OPENJSON functie in de serverloze SQL-pool in Azure Synapse Analytics kan automatisch de identiteit genereren van elke rij die als resultaat wordt geretourneerd. De identiteitskolom wordt opgegeven met behulp van de expressie $.sql:identity() in het JSON-pad na de kolomdefinitie. De kolom met deze waarde in de JSON-padexpressie genereert een uniek getal op basis van 0 voor elk element in de JSON-matrix dat door de functie wordt geparseerd. De identiteitswaarde vertegenwoordigt de positie/index van het matrixelement.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Results

De identiteit is alleen beschikbaar in de serverloze SQL-pool in Synapse Analytics.

with_clause

Definieert expliciet het uitvoerschema voor de OPENJSON functie die moet worden geretourneerd. De optionele with_clause kan de volgende elementen bevatten:

colName

De naam voor de uitvoerkolom.

OPENJSON Standaard wordt de naam van de kolom gebruikt om overeen te komen met een eigenschap in de JSON-tekst. Als u bijvoorbeeld de kolom name in het schema opgeeft, OPENJSON probeert u deze kolom te vullen met de eigenschap 'naam' in de JSON-tekst. U kunt deze standaardtoewijzing overschrijven met behulp van het argument column_path .

type

Het gegevenstype voor de uitvoerkolom.

column_path

Is het JSON-pad waarmee de eigenschap wordt opgegeven die moet worden geretourneerd in de opgegeven kolom. Zie de beschrijving van de padparameter eerder in dit onderwerp voor meer informatie.

Gebruik column_path om standaardtoewijzingsregels te overschrijven wanneer de naam van een uitvoerkolom niet overeenkomt met de naam van de eigenschap.

De vergelijking die wordt gebruikt om padstappen te vergelijken met de eigenschappen van de JSON-expressie, is hoofdlettergevoelig en sorteringsbewust (dat wil gezegd een BIN2-vergelijking).

Zie JSON-padexpressies voor meer informatie over paden.

AS JSON

Gebruik de AS JSON optie in een kolomdefinitie om op te geven dat de eigenschap waarnaar wordt verwezen een binnenste JSON-object of -matrix bevat. Als u de AS JSON optie opgeeft, moet het type kolom nvarchar(MAX) zijn.

• Als u niet opgeeft AS JSON voor een kolom, retourneert de functie een scalaire waarde (bijvoorbeeld int, tekenreeks, waar, onwaar) van de opgegeven JSON-eigenschap op het opgegeven pad. Als het pad een object of matrix vertegenwoordigt en de eigenschap niet kan worden gevonden op het opgegeven pad, retourneert NULL de functie de lax modus of retourneert een fout in strict de modus. Dit gedrag is vergelijkbaar met het gedrag van de JSON_VALUE functie.

• Als u opgeeft AS JSON voor een kolom, retourneert de functie een JSON-fragment van de opgegeven JSON-eigenschap op het opgegeven pad. Als het pad een scalaire waarde vertegenwoordigt en de eigenschap niet kan worden gevonden op het opgegeven pad, retourneert NULL de functie de lax modus of retourneert een fout in strict de modus. Dit gedrag is vergelijkbaar met het gedrag van de JSON_QUERY functie.

Met de volgende query worden bijvoorbeeld de elementen van een matrix geretourneerd en opgemaakt:

DECLARE @json NVARCHAR(MAX) = 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  
    }  
  }
]'  

SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   VARCHAR(200)   '$.Order.Number',  
              Date     DATETIME       '$.Order.Date',  
              Customer VARCHAR(200)   '$.AccountNumber',  
              Quantity INT            '$.Item.Quantity',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

Results

Return value

De kolommen die door de OPENJSON functie worden geretourneerd, zijn afhankelijk van de WITH optie.

• Wanneer u het standaardschema aanroept OPENJSON , dat wil gezegd, wanneer u geen expliciet schema opgeeft in de WITH component, retourneert de functie een tabel met de volgende kolommen:

  • Key. Een nvarchar(4000) waarde die de naam van de opgegeven eigenschap of de index van het element in de opgegeven matrix bevat. De key kolom heeft een BIN2-sortering.

  • Value. Een nvarchar(MAX) -waarde die de waarde van de eigenschap bevat. De value kolom neemt de sortering over van jsonExpression.

  • Type. Een int-waarde die het type van de waarde bevat. De Type kolom wordt alleen geretourneerd wanneer u het standaardschema gebruikt OPENJSON . De type kolom heeft een van de volgende waarden:

    Waarde van de kolom Type	JSON-gegevenstype
    0	null
    1	string
    2	number
    3	true/false
    4	array
    5	object

  Alleen eigenschappen op het eerste niveau worden geretourneerd. De instructie mislukt als de JSON-tekst niet juist is opgemaakt.

• Wanneer u een expliciet schema in de OPENJSON component aanroept WITH en opgeeft, retourneert de functie een tabel met het schema dat u in de WITH component hebt gedefinieerd.

Remarks

json_path gebruikt in het tweede argument van OPENJSON of in with_clause kan beginnen met het lax of strict trefwoord.

• In lax de modus OPENJSON wordt geen fout gegenereerd als het object of de waarde op het opgegeven pad niet kan worden gevonden. Als het pad niet kan worden gevonden, OPENJSON wordt een lege resultatenset of een NULL waarde geretourneerd.
• In strictde modus OPENJSON wordt een fout geretourneerd als het pad niet kan worden gevonden.

Sommige voorbeelden op deze pagina geven expliciet de padmodus op, lax of strict. De padmodus is optioneel. Als u niet expliciet een padmodus opgeeft, lax is de modus de standaardmodus. Zie JSON-padexpressies voor meer informatie over de padmodus en padexpressies.

Kolomnamen in with_clause worden vergeleken met sleutels in de JSON-tekst. Als u de kolomnaam [Address.Country]opgeeft, wordt deze vergeleken met de sleutel Address.Country. Als u wilt verwijzen naar een geneste sleutel Country binnen het object Address, moet u het pad $.Address.Country in het kolompad opgeven.

json_path kan sleutels bevatten met alfanumerieke tekens. Escape the key name in json_path with double quotes if you have special characters in the keys. Komt bijvoorbeeld $."my key $1".regularKey."key with . dot" overeen met de waarde 1 in de volgende JSON-tekst:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Examples

Voorbeeld 1: een JSON-matrix converteren naar een tijdelijke tabel

In het volgende voorbeeld ziet u een lijst met id's als een JSON-matrix met getallen. De query converteert de JSON-matrix naar een tabel met id's en filtert alle producten met de opgegeven id's.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

Deze query is gelijk aan het volgende voorbeeld. In het onderstaande voorbeeld moet u echter getallen insluiten in de query in plaats van ze door te geven als parameters.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

Voorbeeld 2: eigenschappen uit twee JSON-objecten samenvoegen

In het volgende voorbeeld wordt een samenvoeging van alle eigenschappen van twee JSON-objecten geselecteerd. De twee objecten hebben een dubbele naameigenschap . In het voorbeeld wordt de sleutelwaarde gebruikt om de dubbele rij uit te sluiten van de resultaten.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

Voorbeeld 3: Rijen samenvoegen met JSON-gegevens die zijn opgeslagen in tabelcellen met BEHULP VAN KRUIS TOEPASSEN

In het volgende voorbeeld bevat de SalesOrderHeader tabel een SalesReason tekstkolom die een matrix van SalesOrderReasons de JSON-indeling bevat. De SalesOrderReasons objecten bevatten eigenschappen zoals Kwaliteit en Fabrikant. In het voorbeeld wordt een rapport gemaakt dat elke rij met verkooporders aan de gerelateerde verkoopredenen koppelt. De OPENJSON operator breidt de JSON-matrix met verkoopredenen uit alsof de redenen zijn opgeslagen in een afzonderlijke onderliggende tabel. Vervolgens voegt de CROSS APPLY operator elke rij met verkooporders toe aan de rijen die worden geretourneerd door de OPENJSON tabelwaardefunctie.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

Dezelfde query kan opnieuw worden geschreven met behulp van OPENJSON een expliciet gedefinieerd schema van rijen om te retourneren:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

In dit voorbeeld verwijst het $ pad naar elk element in de matrix. Als u de geretourneerde waarde expliciet wilt casten, kunt u dit type query gebruiken.

Voorbeeld 4: Relationele rijen en JSON-elementen combineren met CROSS APPLY

De volgende query combineert relationele rijen en JSON-elementen in de resultaten die worden weergegeven in de volgende tabel.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Results

Voorbeeld 5: JSON-gegevens importeren in SQL Server

In het volgende voorbeeld wordt een heel JSON-object in een SQL Server-tabel geladen.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  

  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

Voorbeeld 6: eenvoudig voorbeeld met JSON-inhoud

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Related content

• JSON-padexpressies
• JSON-gegevens parseren en transformeren met OPENJSON
• OPENJSON gebruiken met het standaardschema
• OPENJSON gebruiken met een expliciet schema