OPENJSON (Transact-SQL)

Platí pro: SQL Server 2016 (13.x) a novější verze koncového bodu Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics v Microsoft Fabric Warehouse v databázi Microsoft Fabric SQL v Microsoft Fabric

Funkce OPENJSON s hodnotou tabulky parsuje text JSON a vrací objekty a vlastnosti ze vstupu JSON jako řádky a sloupce. Jinými slovy, OPENJSON poskytuje zobrazení sady řádků v dokumentu JSON. Sloupce v sadě řádků a cesty vlastností JSON, které se používají k naplnění sloupců, můžete explicitně zadat. Vzhledem k tomu, že OPENJSON vrátí sadu řádků, můžete použít OPENJSON v FROM klauzuli příkazu Transact-SQL stejně jako můžete použít jakoukoli jinou tabulku, zobrazení nebo funkci s hodnotou tabulky.

Slouží OPENJSON k importu dat JSON do SQL Serveru nebo k převodu dat JSON do relačního formátu pro aplikaci nebo službu, která nemůže přímo využívat JSON.

Note

Funkce OPENJSON je dostupná pouze na úrovni kompatibility 130 nebo vyšší. Pokud je úroveň kompatibility databáze nižší než 130, SQL Server nemůže najít a spustit OPENJSON funkci. Další funkce JSON jsou k dispozici na všech úrovních kompatibility.

Úroveň kompatibility můžete zkontrolovat v zobrazení sys.databases nebo ve vlastnostech databáze. Úroveň kompatibility databáze můžete změnit pomocí následujícího příkazu:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL konvence syntaxe

Syntax

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

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

Funkce OPENJSON s hodnotou tabulky parsuje jsonExpression zadaný jako první argument a vrátí jeden nebo více řádků obsahujících data z objektů JSON ve výrazu. JsonExpression může obsahovat vnořené dílčí objekty. Pokud chcete parsovat dílčí objekt z jsonExpression, můžete zadat parametr cesty pro dílčí objekt JSON.

openjson

Diagram syntaxe pro OPENJSON TVF

Ve výchozím nastavení OPENJSON vrátí funkce s hodnotou tabulky tři sloupce, které obsahují název klíče, hodnotu a typ každé key:value dvojice nalezené v jsonExpression. Jako alternativu můžete explicitně zadat schéma sady výsledků, která OPENJSON se vrátí, zadáním with_clause.

with_clause

Diagram syntaxe klauzule WITH v OPENJSON TVF

With_clause obsahuje seznam sloupců s jejich typy, které se mají OPENJSON vrátit. Ve výchozím nastavení OPENJSON odpovídá klíčům v jsonExpression s názvy sloupců v with_clause (v tomto případě odpovídá klíčům, že rozlišují malá a velká písmena). Pokud název sloupce neodpovídá názvu klíče, můžete zadat volitelný column_path, což je výraz cesty JSON , který odkazuje na klíč v rámci jsonExpression.

Arguments

jsonExpression

Je výraz znaku Unicode obsahující text JSON.

OPENJSON iteruje nad prvky pole nebo vlastností objektu ve výrazu JSON a vrátí jeden řádek pro každý prvek nebo vlastnost. Následující příklad vrátí každou vlastnost objektu poskytnutého jako 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:

klíč	value	typ
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

DoublePrecisionFloatingPoint_value odpovídá standardu IEEE-754.

path

Je volitelný výraz cesty JSON, který odkazuje na objekt nebo pole v rámci jsonExpression. OPENJSON vyhledá text JSON na zadané pozici a parsuje pouze odkazovaný fragment. Další informace najdete v tématu Výrazy cesty JSON.

Proměnnou můžete zadat jako hodnotu cesty. (Sql Server 2016 (13.x) a starší verze se nepodporuje.)

Následující příklad vrátí vnořený objekt zadáním cesty:

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

Při OPENJSON analýze pole JSON vrátí funkce indexy prvků v textu JSON jako klíče.

Porovnání používané k porovnávání kroků cesty s vlastnostmi výrazu JSON je citlivé na malá a velká písmena a kolace beze změny (to znamená porovnání BIN2).

Identita elementu Array

OPENJSON funkce v bezserverovém fondu SQL ve službě Azure Synapse Analytics může automaticky generovat identitu každého řádku, který se vrátí v důsledku toho. Sloupec identity je určen pomocí výrazu $.sql:identity() v cestě JSON za definicí sloupce. Sloupec s touto hodnotou ve výrazu cesty JSON vygeneruje jedinečné číslo založené na 0 pro každý prvek v poli JSON, který funkce parsuje. Hodnota identity představuje pozici/index prvku pole.

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
Poškodit	15	2
Apr	17	3
May	23	4
Jun	27	5

Identita je dostupná pouze v bezserverovém fondu SQL ve službě Synapse Analytics.

with_clause

Explicitně definuje výstupní schéma pro funkci, OPENJSON která se má vrátit. Volitelné with_clause mohou obsahovat následující prvky:

colName

Název výstupního sloupce.

Ve výchozím nastavení používá název sloupce ke OPENJSON shodě vlastnosti v textu JSON. Pokud například zadáte sloupec name ve schématu, OPENJSON pokusí se tento sloupec naplnit vlastností "name" v textu JSON. Toto výchozí mapování můžete přepsat pomocí argumentu column_path .

type

Datový typ pro výstupní sloupec.

Note

Pokud použijete tuto možnost, musí být datový typ sloupce nvarchar(MAX).a0>

column_path

Je cesta JSON, která určuje vlastnost, která se má vrátit v zadaném sloupci. Další informace najdete v popisu parametru cesty dříve v tomto tématu.

Pokud název výstupního sloupce neodpovídá názvu vlastnosti, použijte column_path k přepsání výchozích pravidel mapování.

Porovnání používané k porovnávání kroků cesty s vlastnostmi výrazu JSON je citlivé na malá a velká písmena a kolace beze změny (to znamená porovnání BIN2).

Další informace o cestách najdete v tématu Výrazy cesty JSON.

JAKO JSON

AS JSON Pomocí možnosti v definici sloupce určete, že odkazovaná vlastnost obsahuje vnitřní objekt NEBO pole JSON. Pokud zadáte AS JSON možnost, typ sloupce musí být nvarchar(MAX).

Pokud pro sloupec nezadáte AS JSON , vrátí funkce skalární hodnotu (například int, string, true, false) ze zadané vlastnosti JSON v zadané cestě. Pokud cesta představuje objekt nebo pole a vlastnost nelze najít v zadané cestě, vrátí funkce NULL v lax režimu nebo vrátí chybu v strict režimu. Toto chování je podobné chování JSON_VALUE funkce.
Pokud zadáte AS JSON sloupec, vrátí funkce fragment JSON ze zadané vlastnosti JSON v zadané cestě. Pokud cesta představuje skalární hodnotu a vlastnost nelze najít v zadané cestě, vrátí NULL funkce v lax režimu nebo vrátí chybu v strict režimu. Toto chování je podobné chování JSON_QUERY funkce.

Note

Pokud chcete vrátit vnořený fragment JSON z vlastnosti JSON, musíte příznak zadat AS JSON . Pokud tuto možnost nenajdete, OPENJSON vrátí NULL místo odkazovaného objektu NEBO pole JSON hodnotu nebo vrátí chybu za běhu v strict režimu.

Například následující dotaz vrátí a naformátuje prvky pole:

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-31T00:00:00	AW29825	1	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Číslo":"SO43661","Datum":"2011-06-01T00:00:00"}

Návratová hodnota

Sloupce, které OPENJSON funkce vrátí, závisí na WITH možnosti.

Při volání OPENJSON s výchozím schématem – to znamená, že pokud v klauzuli nezadáte explicitní schéma WITH – vrátí funkce tabulku s následujícími sloupci:
- Key. Hodnota nvarchar(4000), která obsahuje název zadané vlastnosti nebo index prvku v zadaném poli. Sloupec key má kolaci BIN2.
- Value. Hodnota nvarchar(MAX), která obsahuje hodnotu vlastnosti. Sloupec value zdědí kolaci z jsonExpression.
- Type. Int hodnota, která obsahuje typ hodnoty. Sloupec Type se vrátí pouze při použití OPENJSON s výchozím schématem. Sloupec type má jednu z následujících hodnot:
  
  Hodnota sloupce Typ Datový typ JSON
  
  0 null
  
  1 řetězec
  
  2 number
  
  3 true/false
  
  4 pole
  
  5 objekt
Vrátí se pouze vlastnosti první úrovně. Příkaz selže, pokud text JSON není správně naformátovaný.
Při volání OPENJSON a zadání explicitního schématu WITH v klauzuli vrátí funkce tabulku se schématem, které jste definovali WITH v klauzuli.

Hodnota sloupce Typ	Datový typ JSON
0	null
1	řetězec
2	number
3	true/false
4	pole
5	objekt

Note

Funkce Key, Valuea Type sloupce jsou vráceny pouze v případě, že používáte OPENJSON s výchozím schématem a nejsou k dispozici s explicitním schématem.

Remarks

json_path použitý v druhém argumentu OPENJSON nebo v with_clause může začínat klíčovým slovem nebo lax klíčovým slovemstrict.

V lax režimu nevyvolá chybu, OPENJSON pokud se objekt nebo hodnota zadané cesty nenašel. Pokud cestu nelze najít, OPENJSON vrátí prázdnou NULL sadu výsledků nebo hodnotu.
V strictrežimu vrátí OPENJSON chybu, pokud se cesta nenašla.

Některé příklady na této stránce explicitně určují režim cesty nebo laxstrict. Režim cesty je volitelný. Pokud explicitně nezadáte režim cesty, lax je výchozí režim. Další informace o režimu cesty a výrazech cesty najdete v tématu Výrazy cesty JSON.

Názvy sloupců v with_clause se shodují s klíči v textu JSON. Pokud zadáte název [Address.Country]sloupce, bude se shodovat s klíčem Address.Country. Pokud chcete odkazovat na vnořený klíč Country v rámci objektu Address, musíte zadat cestu v cestě $.Address.Country ke sloupci.

json_path mohou obsahovat klíče s alfanumerickými znaky. Pokud máte speciální znaky v klíčích, uvozovek u json_path vozovek u $."my key $1".regularKey."key with . dot" Například odpovídá hodnotě 1 v následujícím textu JSON:

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

Examples

Příklad 1 – Převod pole JSON na dočasnou tabulku

Následující příklad obsahuje seznam identifikátorů jako pole čísel JSON. Dotaz převede pole JSON na tabulku identifikátorů a vyfiltruje všechny produkty se zadanými ID.

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

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

Tento dotaz je ekvivalentní následujícímu příkladu. V následujícím příkladu ale musíte do dotazu vložit čísla místo jejich předání jako parametry.

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

Příklad 2 – Sloučení vlastností ze dvou objektů JSON

Následující příklad vybere sjednocení všech vlastností dvou objektů JSON. Dva objekty mají duplicitní vlastnost názvu . Příklad používá hodnotu klíče k vyloučení duplicitního řádku z výsledků.

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

Příklad 3 – Spojení řádků s daty JSON uloženými v buňkách tabulky pomocí příkazu CROSS APPLY

V následujícím příkladu SalesOrderHeader má SalesReason tabulka textový sloupec, který obsahuje pole SalesOrderReasons ve formátu JSON. Objekty SalesOrderReasons obsahují vlastnosti, jako je Kvalita a Výrobce. Příklad vytvoří sestavu, která spojí každý řádek prodejní objednávky se souvisejícími prodejními důvody. Operátor OPENJSON rozšiřuje pole JSON z důvodů prodeje, jako by důvody byly uloženy v samostatné podřízené tabulce. CROSS APPLY Operátor pak spojí každý řádek prodejní objednávky s řádky vrácené OPENJSON funkcí s hodnotou tabulky.

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

Tip

Když potřebujete rozbalit pole JSON uložená v jednotlivých polích a spojit je s nadřazenými řádky, obvykle používáte operátor Transact-SQL CROSS APPLY . Další informace o CROSS APPLYklauzuli FROM

Stejný dotaz lze přepsat pomocí OPENJSON explicitně definovaného schématu řádků, které se mají vrátit:

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

V tomto příkladu $ cesta odkazuje na každý prvek pole. Pokud chcete vrácenou hodnotu explicitně přetypovat, můžete použít tento typ dotazu.

Příklad 4 – Kombinování relačních řádků a elementů JSON s CROSS APPLY

Následující dotaz kombinuje relační řádky a prvky JSON do výsledků zobrazených v následující tabulce.

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	PSČ	lon	lat
Trhy s celým jídlem	Redmond Way z roku 17991	WA 98052	47.666124	-122.10155
Sears	148. Ave NE	WA 98052	47.63024	-122.141246,17

Příklad 5 – Import dat JSON do SQL Serveru

Následující příklad načte celý objekt JSON do tabulky SQL Serveru.

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

Příklad 6 – jednoduchý příklad s obsahem JSON

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

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-11-18

Sdílet prostřednictvím

OPENJSON (Transact-SQL)

Syntax

openjson

with_clause

Arguments

jsonExpression

path

Identita elementu Array

with_clause

colName

type

column_path

JAKO JSON

Návratová hodnota

Remarks

Examples

Příklad 1 – Převod pole JSON na dočasnou tabulku

Příklad 2 – Sloučení vlastností ze dvou objektů JSON

Příklad 3 – Spojení řádků s daty JSON uloženými v buňkách tabulky pomocí příkazu CROSS APPLY

Příklad 4 – Kombinování relačních řádků a elementů JSON s CROSS APPLY

Příklad 5 – Import dat JSON do SQL Serveru

Příklad 6 – jednoduchý příklad s obsahem JSON

Související obsah

Váš názor

Další materiály