OPENJSON (Transact-SQL)

A következőkre vonatkozik: Az SQL Server 2016 (13.x) és újabb verziói Az Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL Analytics végpontja a Microsoft Fabric Warehouse-ban a Microsoft FabricSQL Database-ben a Microsoft Fabricben

A OPENJSON táblaértékű függvény elemzi a JSON-szöveget, és sorként és oszlopként adja vissza a JSON-bemenet objektumait és tulajdonságait. Más szóval egy OPENJSON sorhalmaz nézetet biztosít egy JSON-dokumentumon keresztül. Explicit módon megadhatja a sorhalmaz oszlopait és az oszlopok feltöltéséhez használt JSON-tulajdonság elérési útjait. Mivel OPENJSON sorkészletet ad vissza, ugyanúgy használhatja OPENJSON a FROM Transact-SQL utasítás záradékát, mint bármely más tábla-, nézet- vagy táblaértékű függvényt.

JSON-adatok OPENJSON importálása AZ SQL Serverbe, vagy JSON-adatok relációs formátumba konvertálása olyan alkalmazásokhoz vagy szolgáltatásokhoz, amelyek nem tudják közvetlenül felhasználni a JSON-t.

Note

A OPENJSON függvény csak a 130-es vagy újabb kompatibilitási szinten érhető el. Ha az adatbázis kompatibilitási szintje 130-nál alacsonyabb, az SQL Server nem találja és nem futtatja a függvényt OPENJSON . A többi JSON-függvény minden kompatibilitási szinten elérhető.

A kompatibilitási szintet sys.databases nézetben vagy az adatbázis tulajdonságaiban ellenőrizheti. Az adatbázis kompatibilitási szintjét az alábbi paranccsal módosíthatja:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL szintaxis konvenciók

Syntax

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

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

A OPENJSON táblaértékű függvény az első argumentumként megadott jsonExpression függvényt elemzi, és egy vagy több olyan sort ad vissza, amely a kifejezés JSON-objektumaiból származó adatokat tartalmaz. A jsonExpression beágyazott alobjektumokat tartalmazhat. Ha egy alobjektumot szeretne elemezni a jsonExpressionon belülről, megadhatja a JSON-alobjektum elérési útvonalparaméterét .

openjson

Az OPENJSON TVF szintaxisának diagramja.

Alapértelmezés szerint a OPENJSON táblaértékű függvény három oszlopot ad vissza, amelyek tartalmazzák a key:value található kulcsnevet, értéket és az egyes párok típusát. Másik lehetőségként explicit módon megadhatja a visszaadott eredményhalmaz OPENJSON sémáját with_clause megadásával.

with_clause

Az OPENJSON TVF WITH záradékának szintaxisának diagramja.

A with_clause azoknak az oszlopoknak a listáját tartalmazza, amelyek típusait OPENJSON vissza szeretné adni. Alapértelmezés szerint OPENJSON a jsonExpressionban lévő kulcsokat a with_clause oszlopneveivel egyezik meg (ebben az esetben az egyezéskulcsok azt jelentik, hogy megkülönbözteti a kis- és nagybetűket). Ha egy oszlopnév nem egyezik a kulcs nevével, megadhat egy választható column_path, amely egy JSON-elérésiút-kifejezés , amely a jsonExpressionban lévő kulcsra hivatkozik.

Arguments

jsonExpression

JSON-szöveget tartalmazó Unicode-karakterkifejezés.

Az OPENJSON a tömb elemeit vagy az objektum tulajdonságait a JSON-kifejezésben iterálja, és minden egyes elemhez vagy tulajdonsághoz egy sort ad vissza. Az alábbi példa az objektum minden tulajdonságát jsonExpression néven adja vissza:

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:

kulcs	value	típus
String_value	John	1
DoublePrecisionFloatingPoint_value	45	2
DoublePrecisionFloatingPoint_value	2.3456	2
BooleanTrue_value	true	3
BooleanFalse_value	false	3
Null_value	NULL	0
Array_value	["a","r","r","a","y"]	4
Object_value	{"obj":"ect"}	5

A DoublePrecisionFloatingPoint_value megfelel az IEEE-754-nek.

path

Egy nem kötelező JSON-elérési út kifejezés, amely egy jsonExpression-objektumra vagy tömbre hivatkozik. OPENJSON megkeresi a JSON-szöveget a megadott pozícióban, és csak a hivatkozott töredéket elemzi. További információ: JSON Path Expressions.

Megadhat egy változót az elérési út értékeként. (Ez az SQL Server 2016 (13.x) és korábbi verzióiban nem támogatott.)

Az alábbi példa egy beágyazott objektumot ad vissza az elérési út megadásával:

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

Key	Value
0	en-GB
1	en-UK
2	de-AT
3	es-AR
4	sr-Cyrl

JSON-tömb elemzésekor OPENJSON a függvény kulcsként adja vissza a JSON-szöveg elemeinek indexeit.

Az elérésiút-lépéseknek a JSON-kifejezés tulajdonságaival való egyeztetéséhez használt összehasonlítás a kis- és nagybetűk megkülönböztetése és a rendezés nélküli (azaz BIN2-összehasonlítás).

Tömbelem identitása

az OPENJSON Azure Synapse Analytics kiszolgáló nélküli SQL-készletében található függvény automatikusan létrehozhatja az eredményül visszaadott sorok identitását. Az identitásoszlop az oszlopdefiníció utáni JSON-elérési út kifejezésével $.sql:identity() van megadva. A JSON-elérési út kifejezésében szereplő értékkel rendelkező oszlop egyedi 0-alapú számot hoz létre a függvény által elemezett JSON-tömb minden eleméhez. Az identitás értéke a tömbelem pozícióját/indexét jelöli.

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

month	temp	month_id
Jan	10	0
Feb	12	1
Mar	15	2
Apr	17	3
May	23	4
Jun	27	5

Az identitás csak a Synapse Analytics kiszolgáló nélküli SQL-készletében érhető el.

with_clause

Explicit módon határozza meg a függvény visszatérési sémáját OPENJSON . Az opcionális with_clause a következő elemeket tartalmazhatják:

colName

A kimeneti oszlop neve.

Alapértelmezés szerint OPENJSON az oszlop nevének használatával egyezik a JSON-szöveg egyik tulajdonságával. Ha például a sémában adja meg az oszlopot name , OPENJSON a JSON-szövegben a "name" tulajdonsággal próbálja feltölteni ezt az oszlopot. Ezt az alapértelmezett leképezést felülbírálhatja a column_path argumentum használatával.

type

A kimeneti oszlop adattípusa.

Note

Ha ezt a AS JSON lehetőséget is használja, az oszlop adattípusának nvarchar(MAX) értéknek kell lennie.

column_path

Az a JSON-elérési út, amely a megadott oszlopban visszaadni kívánt tulajdonságot adja meg. További információkért tekintse meg a jelen témakör korábbi elérésiút-paraméterének leírását.

A column_path használatával felülbírálhatja az alapértelmezett leképezési szabályokat, ha a kimeneti oszlop neve nem egyezik meg a tulajdonság nevével.

További információ az elérési utakról: JSON Path Expressions.

JSON-KÉNT

AS JSON Az oszlopdefinícióban található beállítással megadhatja, hogy a hivatkozott tulajdonság tartalmaz-e belső JSON-objektumot vagy tömböt. Ha megadja a AS JSON beállítást, az oszlop típusának nvarchar(MAX)-nak kell lennie.

Ha nem ad meg AS JSON oszlopot, a függvény egy skaláris értéket (például int, string, true, false) ad vissza a megadott elérési út megadott JSON tulajdonságából. Ha az elérési út egy objektumot vagy tömböt jelöl, és a tulajdonság nem található a megadott elérési úton, a függvény módban tér vissza NULLlax , vagy módban hibát strict ad vissza. Ez a viselkedés hasonló a függvény viselkedéséhez JSON_VALUE .
Ha egy oszlopot ad meg AS JSON , a függvény egy JSON-töredéket ad vissza a megadott elérési út megadott JSON-tulajdonságából. Ha az elérési út skaláris értéket jelöl, és a tulajdonság nem található a megadott elérési úton, a függvény módban tér vissza NULLlax , vagy hibát strict ad vissza módban. Ez a viselkedés hasonló a függvény viselkedéséhez JSON_QUERY .

Note

Ha egy beágyazott JSON-töredéket szeretne visszaadni egy JSON-tulajdonságból, meg kell adnia a jelölőt AS JSON . Ha ez a beállítás nem található, OPENJSON akkor a hivatkozott JSON-objektum vagy tömb helyett egy NULL értéket ad vissza, vagy futásidejű hibát strict ad vissza módban.

Az alábbi lekérdezés például egy tömb elemeit adja vissza és formázja:

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

Number	Date	Customer	Quantity	Order
SO43659	2011-05-31T0:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Visszaadott érték

A függvény által OPENJSON visszaadott oszlopok a WITH beállítástól függnek.

Ha az alapértelmezett sémával hív , OPENJSON vagyis ha nem ad meg explicit sémát a WITH záradékban, a függvény a következő oszlopokkal rendelkező táblát ad vissza:
- Key. Egy nvarchar(4000) érték, amely a megadott tulajdonság nevét vagy az elem indexét tartalmazza a megadott tömbben. Az key oszlop BIN2 rendezéssel rendelkezik.
- Value. A tulajdonság értékét tartalmazó nvarchar(MAX) érték. Az value oszlop a rendezést a jsonExpressiontól örökli.
- Type. Az érték típusát tartalmazó int érték. Az Type oszlop csak akkor lesz visszaadva, ha az alapértelmezett sémát használja OPENJSON . Az type oszlop az alábbi értékek egyikével rendelkezik:
  
  A Típus oszlop értéke JSON-adattípus
  
  0 null
  
  1 karakterlánc
  
  2 number
  
  3 true/false
  
  4 tömb
  
  5 objektum
A rendszer csak az első szintű tulajdonságokat adja vissza. Az utasítás meghiúsul, ha a JSON-szöveg nincs megfelelően formázva.
Amikor meghív OPENJSON egy explicit sémát a WITH záradékban, a függvény egy táblát ad vissza a záradékban WITH meghatározott sémával.

A Típus oszlop értéke	JSON-adattípus
0	null
1	karakterlánc
2	number
3	true/false
4	tömb
5	objektum

Note

A Keyrendszer csak akkor adja vissza a , Valueés Type oszlopokat, ha az alapértelmezett sémát használja OPENJSON , és explicit sémával nem érhető el.

Remarks

A with_clause második argumentumában OPENJSON használt json_path a laxstrict kulcsszóval kezdődhetnek.

Módban lax nem okoz hibát, OPENJSON ha a megadott elérési út objektuma vagy értéke nem található. Ha az elérési út nem található, OPENJSON üres eredményhalmazt vagy NULL értéket ad vissza.
Ebben stricta módban OPENJSON hiba jelenik meg, ha az elérési út nem található.

Néhány példa ezen a lapon kifejezetten megadja az elérési utat, lax vagy strict. Az elérési út mód nem kötelező. Ha nem ad meg explicit módon elérési utat, lax akkor a mód az alapértelmezett. Az elérésiút-módról és az elérésiút-kifejezésekről további információt a JSON-elérésiút-kifejezések című témakörben talál.

A with_clause oszlopnevei megegyeznek a JSON-szöveg kulcsaival. Ha megadja az oszlop nevét [Address.Country], akkor az megegyezik a kulccsal Address.Country. Ha egy beágyazott kulcsra Country szeretne hivatkozni az objektumon Addressbelül, meg kell adnia az elérési utat $.Address.Country az oszlop elérési útján.

json_path tartalmazhat alfanumerikus karaktereket tartalmazó kulcsokat. Ha speciális karakterek vannak a kulcsokban, json_path a kulcs nevét idézőjelekkel ússza meg. Például $."my key $1".regularKey."key with . dot" egyezik az alábbi JSON-szöveg értékével 1 :

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

Examples

1. példa – JSON-tömb átalakítása ideiglenes táblává

Az alábbi példa az azonosítók listáját tartalmazza számokat tartalmazó JSON-tömbként. A lekérdezés a JSON-tömböt azonosítótáblává alakítja, és a megadott azonosítókkal szűri az összes terméket.

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

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

Ez a lekérdezés egyenértékű az alábbi példával. Az alábbi példában azonban számokat kell beágyaznia a lekérdezésbe ahelyett, hogy paraméterekként adja át őket.

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

2. példa – Tulajdonságok egyesítése két JSON-objektumból

Az alábbi példa két JSON-objektum összes tulajdonságának egyesítését jelöli ki. A két objektumnak duplikált névtulajdonságuk van. A példa a kulcsérték használatával zárja ki az ismétlődő sort az eredményekből.

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

3. példa – Sorok összekapcsolása táblázatcellákban tárolt JSON-adatokkal a CROSS APPLY használatával

Az alábbi példában a SalesOrderHeader táblázat egy SalesReason JSON formátumú tömböt SalesOrderReasons tartalmazó szöveges oszlopot tartalmaz. Az SalesOrderReasons objektumok olyan tulajdonságokat tartalmaznak, mint a Minőség és a Gyártó. A példa létrehoz egy jelentést, amely minden értékesítési rendeléssort összekapcsol a kapcsolódó értékesítési okokból. Az OPENJSON operátor úgy bontja ki az értékesítési okok JSON-tömbét, mintha az okok külön gyermektáblában lennének tárolva. Ezután az CROSS APPLY operátor az egyes értékesítési rendelési sorokat a táblaértékfüggvény által OPENJSON visszaadott sorokhoz illeszti.

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

Tip

Ha ki kell bontania az egyes mezőkben tárolt JSON-tömböket, és össze kell őket illesztenie a szülősorokkal, általában a Transact-SQL CROSS APPLY operátort kell használnia. További információ: CROSS APPLYFROM záradék.

Ugyanez a lekérdezés újraírható OPENJSON egy explicit módon definiált sorsémával a visszatéréshez:

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

Ebben a példában az elérési út a $ tömb minden elemére hivatkozik. Ha explicit módon szeretné leadni a visszaadott értéket, használhatja ezt a lekérdezéstípust.

4. példa – Relációs sorok és JSON-elemek kombinálása a CROSS APPLY használatával

Az alábbi lekérdezés relációs sorokat és JSON-elemeket egyesít az alábbi táblázatban látható eredményekkel.

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

title	street	irányítószám	lon	lat
Teljes élelmiszerpiacok	17991 Redmond Way	WA 98052	47.666124	-122.10155
Sears	148. Ave NE	WA 98052	47.63024	-122.141246,17

5. példa – JSON-adatok importálása az SQL Serverbe

Az alábbi példa egy teljes JSON-objektumot tölt be egy SQL Server-táblába.

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

6. példa – Egyszerű példa JSON-tartalommal

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

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2025-11-19

Megosztás a következőn keresztül:

OPENJSON (Transact-SQL)

Syntax

openjson

with_clause

Arguments

jsonExpression

path

Tömbelem identitása

with_clause

colName

type

column_path

JSON-KÉNT

Visszaadott érték

Remarks

Examples

1. példa – JSON-tömb átalakítása ideiglenes táblává

2. példa – Tulajdonságok egyesítése két JSON-objektumból

3. példa – Sorok összekapcsolása táblázatcellákban tárolt JSON-adatokkal a CROSS APPLY használatával

4. példa – Relációs sorok és JSON-elemek kombinálása a CROSS APPLY használatával

5. példa – JSON-adatok importálása az SQL Serverbe

6. példa – Egyszerű példa JSON-tartalommal

Kapcsolódó tartalom

Visszajelzés

További források