OPENJSON (Transact-SQL)

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje usługi Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics — punkt końcowy analizy SQL w usłudze Microsoft Fabric Warehouse wbazie danych Microsoft Fabric SQL Database w usłudze Microsoft Fabric

OPENJSON Funkcja o wartości tabeli analizuje tekst JSON i zwraca obiekty i właściwości z danych wejściowych JSON jako wierszy i kolumn. Innymi słowy, OPENJSON udostępnia widok zestawu wierszy w dokumencie JSON. Możesz jawnie określić kolumny w zestawie wierszy i ścieżki właściwości JSON używane do wypełniania kolumn. Ponieważ OPENJSON zwraca zestaw wierszy, można użyć OPENJSON w FROM klauzuli instrukcji Transact-SQL, tak jak można użyć dowolnej innej funkcji tabeli, widoku lub tabeli.

Służy OPENJSON do importowania danych JSON do programu SQL Server lub konwertowania danych JSON na format relacyjny dla aplikacji lub usługi, która nie może bezpośrednio korzystać z formatu JSON.

Note

Funkcja OPENJSON jest dostępna tylko na poziomie zgodności 130 lub wyższym. Jeśli poziom zgodności bazy danych jest niższy niż 130, program SQL Server nie może odnaleźć i uruchomić OPENJSON funkcji. Inne funkcje JSON są dostępne na wszystkich poziomach zgodności.

Poziom zgodności można sprawdzić w widoku sys.databases lub we właściwościach bazy danych. Poziom zgodności bazy danych można zmienić za pomocą następującego polecenia:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL konwencje składni

Syntax

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

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

OPENJSON Funkcja z wartością tabeli analizuje wartość jsonExpression podaną jako pierwszy argument i zwraca jeden lub więcej wierszy zawierających dane z obiektów JSON w wyrażeniu. jsonExpression może zawierać zagnieżdżone obiekty podrzędne. Jeśli chcesz przeanalizować obiekt podrzędny z poziomu jsonExpression, możesz określić parametr ścieżki dla obiektu podrzędnego JSON.

openjson

Diagram składni dla interfejsu TVF OPENJSON.

Domyślnie OPENJSON funkcja table-valued zwraca trzy kolumny, które zawierają nazwę klucza, wartość i typ każdej key:value pary znalezionej w jsonExpression. Alternatywnie można jawnie określić schemat zestawu wyników zwracanego OPENJSON przez podanie with_clause.

with_clause

Diagram składni klauzuli WITH w pliku OPENJSON TVF.

With_clause zawiera listę kolumn z ich typami do OPENJSON zwrócenia. Domyślnie OPENJSON dopasuje klucze w pliku jsonExpression z nazwami kolumn w with_clause (w tym przypadku dopasowanie kluczy oznacza, że jest uwzględniana wielkość liter). Jeśli nazwa kolumny nie jest zgodna z nazwą klucza, możesz podać opcjonalny column_path, czyli wyrażenia ścieżki JSON odwołujące się do klucza w jsonExpression.

Arguments

jsonExpression.

Jest wyrażeniem znaków Unicode zawierającym tekst JSON.

Plik OPENJSON iteruje elementy tablicy lub właściwości obiektu w wyrażeniu JSON i zwraca jeden wiersz dla każdego elementu lub właściwości. Poniższy przykład zwraca każdą właściwość obiektu podaną 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:

klucz	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 jest zgodna ze standardem IEEE-754.

path

To opcjonalne wyrażenie ścieżki JSON, które odwołuje się do obiektu lub tablicy w pliku jsonExpression. OPENJSON szuka tekstu JSON na określonej pozycji i analizuje tylko przywołyny fragment. Aby uzyskać więcej informacji, zobacz Wyrażenia ścieżki JSON.

Możesz podać zmienną jako wartość ścieżki. (Nie jest to obsługiwane w programie SQL Server 2016 (13.x) i starszych wersjach).

Poniższy przykład zwraca zagnieżdżony obiekt, określając ścieżkę:

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

Podczas OPENJSON analizowania tablicy JSON funkcja zwraca indeksy elementów w tekście JSON jako klucze.

Porównanie używane do dopasowywania kroków ścieżki z właściwościami wyrażenia JSON jest wrażliwe na wielkość liter i sortowanie nieświadome (czyli porównanie BIN2).

Tożsamość elementu tablicy

OPENJSON funkcja w bezserwerowej puli SQL w usłudze Azure Synapse Analytics może automatycznie wygenerować tożsamość każdego wiersza zwróconego w wyniku. Kolumna tożsamości jest określana przy użyciu wyrażenia $.sql:identity() w ścieżce JSON po definicji kolumny. Kolumna z tą wartością w wyrażeniu ścieżki JSON wygeneruje unikatową liczbę 0 dla każdego elementu w tablicy JSON, którą analizuje funkcja. Wartość tożsamości reprezentuje pozycję/indeks elementu tablicy.

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

Tożsamość jest dostępna tylko w bezserwerowej puli SQL w usłudze Synapse Analytics.

with_clause

Jawnie definiuje schemat wyjściowy funkcji OPENJSON do zwrócenia. Opcjonalne with_clause mogą zawierać następujące elementy:

colName

Nazwa kolumny wyjściowej.

Domyślnie OPENJSON używa nazwy kolumny do dopasowania właściwości w tekście JSON. Jeśli na przykład określisz kolumnę name w schemacie, OPENJSON spróbuje wypełnić tę kolumnę właściwością "name" w tekście JSON. To domyślne mapowanie można zastąpić przy użyciu argumentu column_path .

type

Typ danych dla kolumny wyjściowej.

Note

Jeśli używasz AS JSON również opcji, typ danych kolumny musi być nvarchar(MAX).

column_path

Czy ścieżka JSON określa właściwość, która ma być zwracana w określonej kolumnie. Aby uzyskać więcej informacji, zobacz opis parametru ścieżki wcześniej w tym temacie.

Użyj column_path , aby zastąpić domyślne reguły mapowania, gdy nazwa kolumny wyjściowej nie jest zgodna z nazwą właściwości.

Porównanie używane do dopasowywania kroków ścieżki z właściwościami wyrażenia JSON jest wrażliwe na wielkość liter i sortowanie nieświadome (czyli porównanie BIN2).

Aby uzyskać więcej informacji na temat ścieżek, zobacz Wyrażenia ścieżki JSON.

JAKO JSON

AS JSON Użyj opcji w definicji kolumny, aby określić, że przywołyną właściwość zawiera wewnętrzny obiekt lub tablicę JSON. Jeśli określisz AS JSON opcję, typ kolumny musi być nvarchar(MAX).

Jeśli nie określisz AS JSON kolumny, funkcja zwraca wartość skalarną (na przykład int, string, true, false) z określonej właściwości JSON na określonej ścieżce. Jeśli ścieżka reprezentuje obiekt lub tablicę, a właściwość nie można znaleźć w określonej ścieżce, funkcja zwraca NULLlax tryb lub zwraca błąd w strict trybie. To zachowanie jest podobne do zachowania JSON_VALUE funkcji.
Jeśli określisz AS JSON kolumnę, funkcja zwraca fragment JSON z określonej właściwości JSON w określonej ścieżce. Jeśli ścieżka reprezentuje wartość skalarną i nie można odnaleźć właściwości w określonej ścieżce, funkcja zwraca NULLlax tryb lub zwraca błąd w strict trybie. To zachowanie jest podobne do zachowania JSON_QUERY funkcji.

Note

Jeśli chcesz zwrócić zagnieżdżony fragment JSON z właściwości JSON, musisz podać flagę AS JSON . Bez tej opcji, jeśli nie można odnaleźć właściwości, OPENJSON zwraca NULL wartość zamiast przywoływałego obiektu JSON lub tablicy albo zwraca błąd czasu wykonywania w strict trybie.

Na przykład następujące zapytanie zwraca i formatuje elementy tablicy:

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	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Wartość zwracana

Kolumny zwracane OPENJSON przez funkcję zależą od WITH opcji.

W przypadku wywołania OPENJSON przy użyciu domyślnego schematu , czyli gdy nie określisz jawnego schematu w WITH klauzuli , funkcja zwraca tabelę z następującymi kolumnami:
- Key. Wartość nvarchar(4000), która zawiera nazwę określonej właściwości lub indeks elementu w określonej tablicy. Kolumna key ma sortowanie BIN2.
- Value. Wartość nvarchar(MAX), która zawiera wartość właściwości. Kolumna value dziedziczy sortowanie z jsonExpression.
- Type. Wartość int zawierająca typ wartości. Kolumna Type jest zwracana tylko w przypadku użycia OPENJSON z domyślnym schematem. Kolumna type ma jedną z następujących wartości:
  
  Wartość kolumny Typ Typ danych JSON
  
  0 null
  
  1 ciąg
  
  2 number
  
  3 true/false
  
  4 macierz
  
  5 obiekt
Zwracane są tylko właściwości pierwszego poziomu. Instrukcja kończy się niepowodzeniem, jeśli tekst JSON nie jest poprawnie sformatowany.
Po wywołaniu OPENJSON metody i określeniu WITH jawnego schematu w klauzuli funkcja zwraca tabelę ze schematem zdefiniowanym w klauzuli WITH .

Wartość kolumny Typ	Typ danych JSON
0	null
1	ciąg
2	number
3	true/false
4	macierz
5	obiekt

Note

Kolumny Key, Valuei są Type zwracane tylko wtedy, gdy używasz OPENJSON z domyślnym schematem i nie są dostępne w jawnym schemacie.

Remarks

json_path użyty w drugim argumencie OPENJSON lub w with_clause może zaczynać się od słowa kluczowego lax lub strict .

W lax trybie nie zgłasza błędu, OPENJSON jeśli nie można odnaleźć obiektu lub wartości w określonej ścieżce. Jeśli nie można odnaleźć ścieżki, OPENJSON zwraca pusty zestaw wyników lub NULL wartość.
W strictsystemie tryb OPENJSON zwraca błąd, jeśli nie można odnaleźć ścieżki.

Niektóre przykłady na tej stronie jawnie określają tryb lax ścieżki lub strict. Tryb ścieżki jest opcjonalny. Jeśli nie określisz jawnie trybu ścieżki, lax tryb jest domyślny. Aby uzyskać więcej informacji na temat trybu ścieżki i wyrażeń ścieżki, zobacz Wyrażenia ścieżki JSON.

Nazwy kolumn w with_clause są dopasowywane do kluczy w tekście JSON. Jeśli określisz nazwę [Address.Country]kolumny , jest ona zgodna z kluczem Address.Country. Jeśli chcesz odwołać się do klucza Country zagnieżdżonego w obiekcie Address, musisz określić ścieżkę w ścieżce $.Address.Country kolumny.

json_path może zawierać klucze z znakami alfanumerycznymi. W json_path należy ująć nazwę klucza z podwójnymi cudzysłowymi, jeśli w kluczach są znaki specjalne. Na przykład $."my key $1".regularKey."key with . dot" dopasuje wartość 1 w następującym tekście JSON:

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

Examples

Przykład 1 — konwertowanie tablicy JSON na tabelę tymczasową

W poniższym przykładzie przedstawiono listę identyfikatorów jako tablicę liczb JSON. Zapytanie konwertuje tablicę JSON na tabelę identyfikatorów i filtruje wszystkie produkty z określonymi identyfikatorami.

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

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

To zapytanie jest równoważne z poniższym przykładem. Jednak w poniższym przykładzie należy osadzić liczby w zapytaniu zamiast przekazywać je jako parametry.

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

Przykład 2 — scalanie właściwości z dwóch obiektów JSON

Poniższy przykład wybiera połączenie wszystkich właściwości dwóch obiektów JSON. Dwa obiekty mają zduplikowaną właściwość name . W przykładzie użyto wartości klucza, aby wykluczyć z wyników zduplikowany wiersz.

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

Przykład 3 — łączenie wierszy z danymi JSON przechowywanymi w komórkach tabeli przy użyciu funkcji CROSS APPLY

W poniższym przykładzie SalesOrderHeader tabela zawiera kolumnę tekstową SalesReason zawierającą tablicę SalesOrderReasons w formacie JSON. Obiekty SalesOrderReasons zawierają właściwości, takie jak Jakość i Producent. W przykładzie tworzony jest raport, który łączy każdy wiersz zamówienia sprzedaży z powiązanymi przyczynami sprzedaży. Operator OPENJSON rozszerza tablicę JSON przyczyn sprzedaży, tak jakby przyczyny były przechowywane w oddzielnej tabeli podrzędnej. CROSS APPLY Następnie operator łączy każdy wiersz zamówienia sprzedaży do wierszy zwracanych przez funkcję z wartością OPENJSON tabeli.

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

Tip

Gdy musisz rozwinąć tablice JSON przechowywane w poszczególnych polach i połączyć je z wierszami nadrzędnymi, zazwyczaj używasz operatora Transact-SQL CROSS APPLY . Aby uzyskać więcej informacji na temat CROSS APPLYusługi , zobacz klauzulę FROM.

To samo zapytanie można przepisać przy użyciu OPENJSON jawnie zdefiniowanego schematu wierszy do zwrócenia:

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

W tym przykładzie $ ścieżka odwołuje się do każdego elementu w tablicy. Jeśli chcesz jawnie rzutować zwróconą wartość, możesz użyć tego typu zapytania.

Przykład 4 — łączenie wierszy relacyjnych i elementów JSON za pomocą funkcji CROSS APPLY

Poniższe zapytanie łączy wiersze relacyjne i elementy JSON z wynikami przedstawionymi w poniższej tabeli.

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	Kodów pocztowych	lon	lat
Całe rynki żywności	17991 Droga Redmond	WA 98052	47.666124	-122.10155
Sears	148th Ave NE	WA 98052	47.63024	-122.141246,17

Przykład 5 — importowanie danych JSON do programu SQL Server

Poniższy przykład ładuje cały obiekt JSON do tabeli programu SQL Server.

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

Przykład 6 — prosty przykład z zawartością 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

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-11-19

Udostępnij za pomocą

OPENJSON (Transact-SQL)

Syntax

openjson

with_clause

Arguments

jsonExpression.

path

Tożsamość elementu tablicy

with_clause

colName

type

column_path

JAKO JSON

Wartość zwracana

Remarks

Examples

Przykład 1 — konwertowanie tablicy JSON na tabelę tymczasową

Przykład 2 — scalanie właściwości z dwóch obiektów JSON

Przykład 3 — łączenie wierszy z danymi JSON przechowywanymi w komórkach tabeli przy użyciu funkcji CROSS APPLY

Przykład 4 — łączenie wierszy relacyjnych i elementów JSON za pomocą funkcji CROSS APPLY

Przykład 5 — importowanie danych JSON do programu SQL Server

Przykład 6 — prosty przykład z zawartością JSON

Treści powiązane

Sprzężenie zwrotne

Dodatkowe zasoby