使用 OPENJSON 剖析及轉換 JSON 資料

  • 發行項

適用於：SQL Server 2016 (13.x) 及更新版本 Azure SQL DatabaseAzure SQL 受控執行個體Azure Synapse Analytics

OPENJSON 資料列集函數可將 JSON 文字轉換成一組資料列和資料行。 一旦您使用 OPENJSON 將 JSON 集合轉換成資料列集，即可在所傳回的資料上執行任何 SQL 查詢，或將其插入至 SQL Server 資料表。

OPENJSON 函數會接受單一 JSON 物件或 JSON 物件的集合，並將其轉換成一或多個資料列。 根據預設，OPENJSON 函式會傳回下列資料：

  • 從 JSON 物件，此函式會傳回可在第一層找到的所有索引鍵/值組。
  • 從 JSON 陣列，此函式會傳回所有陣列元素及其索引。

您可以新增選擇性 WITH 子句，以提供明確定義輸出結構的結構描述。

選項 1 - 具有預設輸出的 OPENJSON

當您使用 OPENJSON 函式而不提供明確的結果結構描述 (也就是在 OPENJSON 之後不使用 WITH 子句) 時，此函式會傳回包含下列三個資料行的資料表：

  1. 輸入物件中的屬性名稱 (或輸入陣列中元素的索引)。
  2. 屬性或陣列元素的值。
  3. 類型 (例如字串、數字、布林值、陣列或物件)。

OPENJSON 會以個別資料列的方式傳回 JSON 物件的每個屬性，或陣列的每個元素。

以下是簡單的範例，其使用具有預設結構描述 (也就是不提供選擇性 WITH 子句) 的 OPENJSON，並以個別資料列的方式傳回 JSON 物件的每個屬性。

範例：

DECLARE @json NVARCHAR(MAX)

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

SELECT *
FROM OPENJSON(@json);

結果：

索引鍵 value type
NAME John 1
surname Doe 1
age 45 2
skills ["SQL","C#","MVC"] 4

具有預設結構描述之 OPENJSON 的詳細資訊

如需詳細資訊和範例，請參閱搭配使用 OPENJSON 與預設結構描述 (SQL Server)。

如需了解語法和使用方式，請參閱 OPENJSON (Transact-SQL)。

選項 2 - 具有明確結構的 OPENJSON 輸出

當您使用 OPENJSON 函數的 WITH 子句指定結果的結構描述時，此函數會傳回只包含您在 WITH 子句中所定義之資料行的資料表。 在選擇性 WITH 子句中，您可以指定一組輸出資料行、其類型，以及每個輸出值的 JSON 來源屬性路徑。 OPENJSON 會逐一查看 JSON 物件的陣列、讀取為每個資料行指定之路徑上的值，並將值轉換成指定的型。

以下是簡單的範例，其使用具有您在 WITH 子句中明確指定之輸出結構描述的 OPENJSON。

範例：

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

結果：

Number Date 客戶 數量
SO43659 2011-05-31T00:00:00 AW29825 1
SO43661 2011-06-01T00:00:00 AW73565 3

此函數會傳回並格式化為 JSON 陣列的元素。

  • 針對 JSON 陣列中的每個元素， OPENJSON 會在輸出資料表中產生新的資料列。 JSON 陣列中的兩個元素會轉換成所傳回資料表中的兩個資料列。

  • 針對使用 colName type json_path 語法指定的每個資料行，OPENJSON 會將指定路徑上每個陣列元素中所找到的值轉換成指定的類型。 在此範例中，Date 資料行的值取自路徑 $.Order.Date 上的每個元素，並已轉換成日期時間值。

具有明確結構描述之 OPENJSON 的詳細資訊

如需詳細資訊和範例，請參閱使用 OPENJSON 與明確結構描述 (SQL Server)。

如需了解語法和使用方式，請參閱 OPENJSON (Transact-SQL)。

OPENJSON 需要相容性層級 130

OPENJSON 函數僅適用於 相容性層級 130以下。 如果您的資料庫相容性層級低於 130，SQL Server 將找不到且無法執行 OPENJSON 函式。 其他內建 JSON 函數適用於所有的相容性層級。

您可以在 sys.databases 檢視或資料庫屬性中查看相容性層級。

您可以使用下列命令變更資料庫的相容性層級：
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

深入了解 SQL Server 和 Azure SQL Database 中的 JSON

Microsoft 影片

注意

本節中的部分影片連結目前可能無法運作。 Microsoft 正在將先前在 Channel 9 上的內容移轉至新的平台。 我們會在影片移轉至新平台時更新連結。

如需 SQL Server 和 Azure SQL Database 中內建 JSON 支援的觀看式簡介，請參閱下列影片：

另請參閱