使用 OPENJSON 剖析及轉換 JSON 資料 (SQL Server)Parse and Transform JSON Data with OPENJSON (SQL Server)

適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更新版本 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics適用範圍:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

OPENJSON 資料列集函數可將 JSON 文字轉換成一組資料列和資料行。The OPENJSON rowset function converts JSON text into a set of rows and columns. 一旦您使用 OPENJSON 將 JSON 集合轉換成資料列集,即可在所傳回的資料上執行任何 SQL 查詢,或將其插入至 SQL Server 資料表。After you transform a JSON collection into a rowset with OPENJSON, you can run any SQL query on the returned data or insert it into a SQL Server table.

OPENJSON 函數會接受單一 JSON 物件或 JSON 物件的集合,並將其轉換成一或多個資料列。The OPENJSON function takes a single JSON object or a collection of JSON objects and transforms them into one or more rows. 根據預設,OPENJSON 函式會傳回下列資料:By default, the OPENJSON function returns the following data:

  • 從 JSON 物件,此函式會傳回可在第一層找到的所有索引鍵/值組。From a JSON object, the function returns all the key/value pairs that it finds at the first level.
  • 從 JSON 陣列,此函式會傳回所有陣列元素及其索引。From a JSON array, the function returns all the elements of the array with their indexes.

您可以新增選擇性 WITH 子句,以提供明確定義輸出結構的結構描述。You can add an optional WITH clause to provide a schema that explicitly defines the structure of the output.

選項 1 - 具有預設輸出的 OPENJSONOption 1 - OPENJSON with the default output

當您使用 OPENJSON 函式而不提供明確的結果結構描述 (也就是在 OPENJSON 之後不使用 WITH 子句) 時,此函式會傳回包含下列三個資料行的資料表:When you use the OPENJSON function without providing an explicit schema for the results - that is, without a WITH clause after OPENJSON - the function returns a table with the following three columns:

  1. 輸入物件中的屬性 名稱 (或輸入陣列中元素的索引)。The name of the property in the input object (or the index of the element in the input array).
  2. 屬性或陣列元素的 The value of the property or the array element.
  3. 類型 (例如字串、數字、布林值、陣列或物件)。The type (for example, string, number, boolean, array, or object).

OPENJSON 會以個別資料列的方式傳回 JSON 物件的每個屬性,或陣列的每個元素。OPENJSON returns each property of the JSON object, or each element of the array, as a separate row.

以下是簡單的範例,其使用具有預設結構描述 (也就是不提供選擇性 WITH 子句) 的 OPENJSON,並以個別資料列的方式傳回 JSON 物件的每個屬性。Here's a quick example that uses OPENJSON with the default schema - that is, without the optional WITH clause - and returns one row for each property of the JSON object.

範例Example

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

結果Results

索引鍵key valuevalue typetype
NAMEname JohnJohn 11
surnamesurname DoeDoe 11
ageage 4545 22
skillsskills ["SQL","C#","MVC"]["SQL","C#","MVC"] 44

具有預設結構描述之 OPENJSON 的詳細資訊More info about OPENJSON with the default schema

如需詳細資訊和範例,請參閱搭配使用 OPENJSON 與預設結構描述 (SQL Server)For more info and examples, see Use OPENJSON with the Default Schema (SQL Server).

如需語法和使用方式,請參閱 OPENJSON (Transact-SQL)以下。For syntax and usage, see OPENJSON (Transact-SQL).

選項 2 - 具有明確結構的 OPENJSON 輸出Option 2 - OPENJSON output with an explicit structure

當您使用 OPENJSON 函數的 WITH 子句指定結果的結構描述時,此函數會傳回只包含您在 WITH 子句中所定義之資料行的資料表。When you specify a schema for the results by using the WITH clause of the OPENJSON function, the function returns a table with only the columns that you define in the WITH clause. 在選擇性 WITH 子句中,您可以指定一組輸出資料行、其類型,以及每個輸出值的 JSON 來源屬性路徑。In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON 會逐一查看 JSON 物件的陣列、讀取為每個資料行指定之路徑上的值,並將值轉換成指定的型。OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.

以下是簡單的範例,其使用具有您在 WITH 子句中明確指定之輸出結構描述的 OPENJSONHere's a quick example that uses OPENJSON with a schema for the output that you explicitly specify in the WITH clause.

範例Example

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

結果Results

NumberNumber DateDate 客戶Customer 數量Quantity
SO43659SO43659 2011-05-31T00:00:002011-05-31T00:00:00 AW29825AW29825 11
SO43661SO43661 2011-06-01T00:00:002011-06-01T00:00:00 AW73565AW73565 33

此函數會傳回並格式化為 JSON 陣列的元素。This function returns and formats the elements of a JSON array.

  • 針對 JSON 陣列中的每個元素, OPENJSON 會在輸出資料表中產生新的資料列。For each element in the JSON array, OPENJSON generates a new row in the output table. JSON 陣列中的兩個元素會轉換成所傳回資料表中的兩個資料列。The two elements in the JSON array are converted into two rows in the returned table.

  • 針對使用 colName type json_path 語法指定的每個資料行,OPENJSON 會將指定路徑上每個陣列元素中所找到的值轉換成指定的類型。For each column, specified by using the colName type json_path syntax, OPENJSON converts the value found in each array element on the specified path to the specified type. 在此範例中,Date 資料行的值取自路徑 $.Order.Date 上的每個元素,並已轉換成日期時間值。In this example, values for the Date column are taken from each element on the path $.Order.Date and converted to datetime values.

具有明確結構描述之 OPENJSON 的詳細資訊More info about OPENJSON with an explicit schema

如需詳細資訊和範例,請參閱使用 OPENJSON 與明確結構描述 (SQL Server)For more info and examples, see Use OPENJSON with an Explicit Schema (SQL Server).

如需語法和使用方式,請參閱 OPENJSON (Transact-SQL)以下。For syntax and usage, see OPENJSON (Transact-SQL).

OPENJSON 需要相容性層級 130OPENJSON requires Compatibility Level 130

OPENJSON 函數僅適用於 相容性層級 130 以下。The OPENJSON function is available only under compatibility level 130. 如果您的資料庫相容性層級低於 130,SQL Server 將找不到且無法執行 OPENJSON 函式。If your database compatibility level is lower than 130, SQL Server can't find and run the OPENJSON function. 其他內建 JSON 函數適用於所有的相容性層級。Other built-in JSON functions are available at all compatibility levels.

您可以在 sys.databases 檢視或資料庫屬性中查看相容性層級。You can check compatibility level in the sys.databases view or in database properties.

您可以使用下列命令變更資料庫的相容性層級:You can change the compatibility level of a database by using the following command:
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

深入了解 SQL Server 和 Azure SQL Database 中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 影片Microsoft videos

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介,請參閱下列影片:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

另請參閱See Also

OPENJSON (Transact-SQL)OPENJSON (Transact-SQL)