OPENJSON (Transact-SQL)

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL Analytics-slutpunkt i Microsoft Fabric Warehouse i Microsoft FabricSQL Database i Förhandsversion av Microsoft Fabric

Funktionen OPENJSON tabellvärde parsar JSON-text och returnerar objekt och egenskaper från JSON-indata som rader och kolumner. Med andra ord OPENJSON ger en raduppsättningsvy över ett JSON-dokument. Du kan uttryckligen ange kolumnerna i raduppsättningen och JSON-egenskapssökvägarna som används för att fylla i kolumnerna. Eftersom OPENJSON returnerar en uppsättning rader kan du använda OPENJSON i -satsen i FROM en Transact-SQL-instruktion precis som du kan använda andra tabeller, vyer eller tabellvärdesfunktioner.

Använd OPENJSON för att importera JSON-data till SQL Server eller för att konvertera JSON-data till relationsformat för en app eller tjänst som inte kan använda JSON direkt.

Transact-SQL syntaxkonventioner

Syntax

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

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

Funktionen OPENJSON table-valued parsar jsonExpression som anges som det första argumentet och returnerar en eller flera rader som innehåller data från JSON-objekten i uttrycket. jsonExpression kan innehålla kapslade underobjekt. Om du vill parsa ett underobjekt inifrån jsonExpression kan du ange en sökvägsparameter för JSON-underobjektet.

openjson

Som standard OPENJSON returnerar funktionen tabellvärde tre kolumner, som innehåller nyckelnamnet, värdet och typen av varje key:value par som finns i jsonExpression. Alternativt kan du uttryckligen ange schemat för resultatuppsättningen som returneras genom att OPENJSON ange with_clause.

with_clause

With_clause innehåller en lista med kolumner med deras typer som OPENJSON ska returneras. Som standard OPENJSON matchar nycklar i jsonExpression med kolumnnamnen i with_clause (i det här fallet matchar nycklar innebär det att det är skiftlägeskänsligt). Om ett kolumnnamn inte matchar ett nyckelnamn kan du ange en valfri column_path, som är ett JSON Path Expressions som refererar till en nyckel i jsonExpression.

Arguments

jsonExpression

Är ett Unicode-teckenuttryck som innehåller JSON-text.

OPENJSON itererar över elementen i matrisen eller egenskaperna för objektet i JSON-uttrycket och returnerar en rad för varje element eller egenskap. I följande exempel returneras varje egenskap för objektet som anges som 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:

• DoublePrecisionFloatingPoint_value överensstämmer med IEEE-754.

path

Är ett valfritt JSON-sökvägsuttryck som refererar till ett objekt eller en matris i jsonExpression. OPENJSON söker till JSON-texten vid den angivna positionen och parsar endast det refererade fragmentet. Mer information finns i JSON-sökvägsuttryck.

Du kan ange en variabel som värdet för sökvägen. (Detta stöds inte i SQL Server 2016 (13.x) och tidigare versioner.)

I följande exempel returneras ett kapslat objekt genom att ange sökvägen:

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

När OPENJSON parsar en JSON-matris returnerar funktionen indexen för elementen i JSON-texten som nycklar.

Jämförelsen som används för att matcha sökvägssteg med egenskaperna för JSON-uttrycket är skiftlägeskänslig och sorterings-omedveten (d.s. en BIN2-jämförelse).

Matriselementidentitet

OPENJSON funktionen i den serverlösa SQL-poolen i Azure Synapse Analytics kan automatiskt generera identiteten för varje rad som returneras som ett resultat. Identitetskolumnen anges med uttrycket $.sql:identity() i JSON-sökvägen efter kolumndefinitionen. Kolumnen med det här värdet i JSON-sökvägsuttrycket genererar ett unikt 0-baserat tal för varje element i JSON-matrisen som funktionen parsar. Identitetsvärdet representerar matriselementets position/index.

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

Identiteten är endast tillgänglig i den serverlösa SQL-poolen i Synapse Analytics.

with_clause

Definierar uttryckligen utdataschemat för funktionen som OPENJSON ska returneras. Den valfria with_clause kan innehålla följande element:

colName

Namnet på utdatakolumnen.

Som standard OPENJSON använder du namnet på kolumnen för att matcha en egenskap i JSON-texten. Om du till exempel anger kolumnen name i schemat OPENJSON försöker du fylla den här kolumnen med egenskapen "name" i JSON-texten. Du kan åsidosätta den här standardmappningen med hjälp av argumentet column_path .

type

Datatypen för utdatakolumnen.

column_path

Är JSON-sökvägen som anger vilken egenskap som ska returneras i den angivna kolumnen. Mer information finns i beskrivningen av sökvägsparametern tidigare i det här avsnittet.

Använd column_path för att åsidosätta standardmappningsregler när namnet på en utdatakolumn inte matchar namnet på egenskapen.

Jämförelsen som används för att matcha sökvägssteg med egenskaperna för JSON-uttrycket är skiftlägeskänslig och sorterings-omedveten (d.s. en BIN2-jämförelse).

Mer information om sökvägar finns i JSON-sökvägsuttryck.

AS JSON

Använd alternativet AS JSON i en kolumndefinition för att ange att den refererade egenskapen innehåller ett inre JSON-objekt eller en matris. Om du anger alternativet AS JSON måste kolumnens typ vara nvarchar(MAX).

• Om du inte anger AS JSON för en kolumn returnerar funktionen ett skalärt värde (till exempel int, string, true, false) från den angivna JSON-egenskapen på den angivna sökvägen. Om sökvägen representerar ett objekt eller en matris och egenskapen inte kan hittas på den angivna sökvägen, returnerar NULL funktionen i lax läge eller returnerar ett fel i strict läge. Det här beteendet liknar funktionens JSON_VALUE beteende.

• Om du anger AS JSON för en kolumn returnerar funktionen ett JSON-fragment från den angivna JSON-egenskapen på den angivna sökvägen. Om sökvägen representerar ett skalärt värde och egenskapen inte kan hittas på den angivna sökvägen, returnerar NULL funktionen i lax läge eller returnerar ett fel i strict läge. Det här beteendet liknar funktionens JSON_QUERY beteende.

Följande fråga returnerar och formaterar till exempel elementen i en matris:

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

Vilka kolumner som OPENJSON funktionen returnerar beror på alternativet WITH .

• När du anropar OPENJSON med standardschemat – det vill säga när du inte anger ett explicit schema i WITH -satsen – returnerar funktionen en tabell med följande kolumner:

  • Key. Ett nvarchar(4000) -värde som innehåller namnet på den angivna egenskapen eller indexet för elementet i den angivna matrisen. Kolumnen key har en BIN2-sortering.

  • Value. Ett nvarchar(MAX) -värde som innehåller värdet för egenskapen. Kolumnen value ärver sin sortering från jsonExpression.

  • Type. Ett int-värde som innehåller typen av värde. Kolumnen Type returneras endast när du använder OPENJSON med standardschemat. Kolumnen type har något av följande värden:

    Värdet för kolumnen Typ	JSON-datatyp
    0	null
    1	string
    2	number
    3	true/false
    4	array
    5	object

  Endast egenskaper på första nivån returneras. Instruktionen misslyckas om JSON-texten inte är korrekt formaterad.

• När du anropar OPENJSON och anger ett explicit schema i WITH -satsen returnerar funktionen en tabell med det schema som du definierade i WITH -satsen.

Remarks

json_path som används i det andra argumentet OPENJSON i eller i with_clause kan börja med nyckelordet lax eller strict .

• I lax läge OPENJSON genererar inte ett fel om objektet eller värdet på den angivna sökvägen inte kan hittas. Om sökvägen inte kan hittas OPENJSON returnerar antingen en tom resultatuppsättning eller ett NULL värde.
• I strictreturnerar läget OPENJSON ett fel om sökvägen inte kan hittas.

Några av exemplen på den här sidan anger uttryckligen sökvägsläget, lax eller strict. Sökvägsläget är valfritt. Om du inte uttryckligen anger ett sökvägsläge lax är läget standard. Mer information om sökvägsläge och sökvägsuttryck finns i JSON-sökvägsuttryck.

Kolumnnamn i with_clause matchas med nycklar i JSON-texten. Om du anger kolumnnamnet [Address.Country]matchas det med nyckeln Address.Country. Om du vill referera till en kapslad nyckel Country i objektet Addressmåste du ange sökvägen $.Address.Country i kolumnsökvägen.

json_path kan innehålla nycklar med alfanumeriska tecken. Undvik nyckelnamnet i json_path med dubbla citattecken om du har specialtecken i nycklarna. Matchar till exempel $."my key $1".regularKey."key with . dot" värdet 1 i följande JSON-text:

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

Examples

Exempel 1 – Konvertera en JSON-matris till en tillfällig tabell

I följande exempel finns en lista över identifierare som en JSON-matris med tal. Frågan konverterar JSON-matrisen till en tabell med identifierare och filtrerar alla produkter med de angivna ID:na.

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

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

Den här frågan motsvarar följande exempel. I exemplet nedan måste du dock bädda in tal i frågan i stället för att skicka dem som parametrar.

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

Exempel 2 – Sammanfoga egenskaper från två JSON-objekt

I följande exempel väljs en union av alla egenskaper för två JSON-objekt. De två objekten har en dubblettnamnegenskap. I exemplet används nyckelvärdet för att undanta dubblettraden från 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))

Exempel 3 – Koppla rader med JSON-data som lagras i tabellceller med CROSS APPLY

I följande exempel har tabellen SalesOrderHeader en SalesReason textkolumn som innehåller en matris med SalesOrderReasons i JSON-format. Objekten SalesOrderReasons innehåller egenskaper som Kvalitet och Tillverkare. I exemplet skapas en rapport som kopplar varje försäljningsorderrad till relaterade försäljningsorsaker. Operatorn OPENJSON expanderar JSON-matrisen med försäljningsorsaker som om orsakerna lagrades i en separat underordnad tabell. Sedan kopplar operatorn CROSS APPLY varje försäljningsorderrad till de rader som returneras av OPENJSON funktionen table-valued.

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

Samma fråga kan skrivas om med hjälp OPENJSON av ett explicit definierat schema med rader som ska returneras:

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

I det här exemplet $ refererar sökvägen till varje element i matrisen. Om du uttryckligen vill omvandla det returnerade värdet kan du använda den här typen av fråga.

Exempel 4 – Kombinera relationsrader och JSON-element med CROSS APPLY

Följande fråga kombinerar relationsrader och JSON-element i resultaten som visas i följande tabell.

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

Exempel 5 – Importera JSON-data till SQL Server

I följande exempel läses ett helt JSON-objekt in i en SQL Server-tabell.

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

Exempel 6 – Enkelt exempel med JSON-innehåll

--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-sökvägsuttryck
• Parsa och transformera JSON-data med OPENJSON
• Använda OPENJSON med standardschemat
• Använda OPENJSON med ett explicit schema