OPENJSON （Transact-SQL）

適用於： SQL Server 2016 （13.x）及以後版本 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL Analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric SQL database in Microsoft Fabric

OPENJSON數據表值函式會剖析 JSON 文字，並將 JSON 輸入中的物件和屬性當做數據列和數據行傳回。換句話說， OPENJSON 提供 JSON 文件的數據列集檢視。您可以明確指定資料列集中的資料行，以及用來填入資料行的 JSON 屬性路徑。由於 OPENJSON 會傳回一組數據列，因此您可以在 OPENJSON Transact-SQL 語句的子句中使用 FROM ，就像您可以使用任何其他數據表、檢視或數據表值函式一樣。

使用 OPENJSON 將 JSON 數據匯入 SQL Server，或將 JSON 資料轉換成無法直接取用 JSON 之應用程式或服務的關係型格式。

Note

函 OPENJSON 式僅適用於相容性層級 130 或更高版本。如果您的資料庫相容性層級低於 130，SQL Server 找不到並執行函 OPENJSON 式。其他 JSON 函數適用於所有的相容性層級。

您可以在 sys.databases 檢視或資料庫屬性中查看相容性層級。您可以使用下列命令變更資料庫的相容性層級：

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL 語法慣例

Syntax

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

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

OPENJSON數據表值函式會剖析提供做為第一個自變數的 jsonExpression，並傳回一或多個數據列，其中包含表達式中 JSON 對象的數據。 jsonExpression 可以包含巢狀子物件。如果您想要從 jsonExpression 中剖析子物件，您可以指定 JSON 子物件 的路徑 參數。

openjson

OPENJSON TVF 語法的圖表。

根據預設，OPENJSON數據表值函式會傳回三個數據行，其中包含索引鍵名稱、值，以及 key:value 中找到的每個配對類型。或者，您可以藉由提供OPENJSON，明確指定傳回的結果集架構。

with_clause

OPENJSON TVF 中 WITH 子句語法的圖表。

with_clause包含數據行清單，其中包含要傳回的數據行類型OPENJSON。根據預設， OPENJSON 比對 jsonExpression 中的索引鍵與 with_clause 中的數據行名稱（在此案例中，比對索引鍵表示區分大小寫）。如果數據行名稱不符合索引鍵名稱，您可以提供選擇性column_path，這是參考 jsonExpression 內索引鍵的 JSON 路徑表達式。

Arguments

jsonExpression

為包含 JSON 文字的 Unicode 字元運算式。

OPENJSON 會逐一查看陣列中的項目或 JSON 運算式中物件的屬性，並為每個項目或屬性傳回一個資料列。下列範例會傳回以 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:

Key	value	型別
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 符合 IEEE-754 標準。

path

這是參考 jsonExpression 內物件或陣列的選擇性 JSON 路徑表示式。 OPENJSON 會搜尋位於指定位置的 JSON 文字，並只剖析參考的片段。如需詳細資訊，請參閱 JSON 路徑表達式。

您可以提供變數做為路徑的值。（SQL Server 2016 （13.x）和舊版不支援此功能。

下列範例會藉由指定路徑來傳回巢狀物件：

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 陣列時 OPENJSON ，函式會將 JSON 文字中的元素索引當做索引鍵傳回。

用來比對路徑步驟與 JSON 運算式屬性的比較會區分大小寫且為非識別定序 (即 BIN2 比較)。

陣列元素識別

Azure Synapse Analytics 中無伺服器 SQL 集區的 OPENJSON 函數可自動產生每個資料列的識別，以傳回作為結果。此識別欄位是使用資料行定義之後 JSON 路徑中的運算式 $.sql:identity() 來指定。 JSON 路徑運算式中具有此值的資料行，會針對函數所剖析 JSON 陣列中的每個元素，產生以 0 起始的唯一數字。此識別值可表示陣列元素的位置/索引。

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

此識別僅適用於 Synapse Analytics 中的無伺服器 SQL 集區。

with_clause

明確定義要傳回之函式的 OPENJSON 輸出架構。選擇性 with_clause 可以包含下列元素：

名字

輸出數據行的名稱。

根據預設， OPENJSON 會使用數據行的名稱來比對 JSON 文字中的屬性。例如，如果您在架構中指定數據行 name ， OPENJSON 嘗試以 JSON 文字中的屬性「name」填入此資料行。您可以使用 column_path 自變數來覆寫此預設對應。

type

輸出數據行的數據類型。

Note

如果您也使用 AS JSON 選項，資料行資料類型必須是 nvarchar（MAX） 。

column_path

為指定要在指定資料行中傳回之屬性的 JSON 路徑。如需詳細資訊，請參閱本主題先前路徑參數的描述。

當輸出資料行的名稱不符合屬性的名稱時，請使用 column_path 覆寫預設對應規則。

用來比對路徑步驟與 JSON 運算式屬性的比較會區分大小寫且為非識別定序 (即 BIN2 比較)。

如需路徑的詳細資訊，請參閱 JSON 路徑表示式。

AS JSON

使用資料 AS JSON 行定義中的選項，指定參考的屬性包含內部 JSON 物件或陣列。如果您指定 AS JSON 選項，數據行的類型必須是 nvarchar（MAX）。

如果您未 AS JSON 指定數據行，函式會從指定路徑上的指定 JSON 屬性傳回純量值（例如 int、 string、 true、 false）。如果路徑代表物件或陣列，而且在指定的路徑上找不到屬性，則函式會在模式中NULL傳回lax或傳回模式中的strict錯誤。此行為類似於函式 JSON_VALUE 的行為。
如果您為數據行指定 AS JSON ，函式會從指定路徑上的指定 JSON 屬性傳回 JSON 片段。如果路徑代表純量值，而且在指定的路徑上找不到屬性，則函式會在模式中NULL傳回lax或傳回模式中的strict錯誤。此行為類似於函式 JSON_QUERY 的行為。

Note

如果您想要從 JSON 屬性傳回巢狀 JSON 片段，則必須提供 AS JSON 旗標。如果沒有此選項，如果找不到屬性， OPENJSON 則傳回 NULL 值，而不是參考的 JSON 物件或陣列，或在模式中 strict 傳回運行時錯誤。

例如，下列查詢會傳回並格式化陣列的項目：

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

返回值

函式傳回的數據行 OPENJSON 取決於 WITH 選項。

當您使用預設架構呼叫 OPENJSON 時，也就是當您未在子句中 WITH 指定明確架構時，函式會傳回具有下列資料行的數據表：
- Key。 nvarchar（4000） 值，其中包含指定之屬性的名稱或指定數位中專案的索引。數據 key 行具有 BIN2 定序。
- Value。包含屬性值的 nvarchar（MAX） 值。數據 value 行會從 jsonExpression 繼承其定序。
- Type。 int 值，其中包含值的型別。 Type只有在您搭配預設架構使用OPENJSON時，才會傳回數據行。資料 type 列具有下列其中一個值：
  
  「類型」資料行的值 JSON 資料類型
  
  0 null
  
  1 字串
  
  2 number
  
  3 true/false
  
  4 陣列
  
  5 物件
只會傳回第一層的屬性。若 JSON 文字的格式不正確，陳述式便會失敗。
當您在子句中OPENJSON呼叫 WITH 並指定明確的架構時，函式會傳回數據表，其中包含您在子句中WITH定義的架構。

「類型」資料行的值	JSON 資料類型
0	null
1	字串
2	number
3	true/false
4	陣列
5	物件

Note

Key只有在搭配默認架構使用 Value 時，才會傳回、 Type和 OPENJSON 數據行，而且無法搭配明確架構使用。

Remarks

json_path用於或 with_clause 的第二個自變數OPENJSON中，可以從或 lax 關鍵詞開始strict。

在模式中 lax ， OPENJSON 如果找不到指定路徑上的物件或值，則不會引發錯誤。如果找不到路徑， OPENJSON 則傳回空的結果集或 NULL 值。
在中 strict，如果找不到路徑，則模式 OPENJSON 會傳回錯誤。

這個頁面的一些範例會明確指定路徑模式或 laxstrict。路徑模式為選擇性。如果您未明確指定路徑模式， lax 則模式為預設值。如需路徑模式和路徑表達式的詳細資訊，請參閱 JSON 路徑表達式。

with_clause中的數據行名稱會與 JSON 文字中的索引鍵相符。若您指定資料行名稱 [Address.Country]，它便會與索引鍵 Address.Country 進行比對。若您想要參考 Country 物件中的 Address 巢狀索引鍵，您必須在資料行路徑中指定 $.Address.Country 路徑。

json_path 可以包含英數位元的索引鍵。如果您在索引鍵中有特殊字元，請以雙引號逸出 json_path 中的索引鍵名稱。例如， $."my key $1".regularKey."key with . dot" 比對下列 JSON 文字中的值 1 ：

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

Examples

範例 1 - 將 JSON 陣列轉換成暫存資料表

下列範例以一個數字 JSON 陣列提供識別碼清單。查詢會將 JSON 陣列轉換成識別碼資料表，並使用指定的識別碼篩選所有產品。

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

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

這個查詢相當於下列範例。然而，在以下的範例中，您必須在查詢中嵌入數字，而非將它們作為參數傳遞。

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

範例 2 - 合併來自兩個 JSON 物件的屬性

下列範例會選取兩個 JSON 物件的集合聯集。這兩個物件具有重複 的名稱 屬性。範例使用索引鍵值來從結果中排除重複的資料列。

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 - 使用 CROSS APPLY 來使用儲存在表格儲存格中的 JSON 資料聯結資料列

在下列範例中，SalesOrderHeader 資料表有一個 SalesReason 文字資料行，其中包含以 JSON 格式儲存的 SalesOrderReasons 陣列。物件 SalesOrderReasons 包含品質與 製造商等屬性。範例會建立將每個銷售訂單聯結到相關銷售原因的報表。運算符 OPENJSON 會展開銷售原因的 JSON 陣列，就好像原因儲存在個別的子數據表中一樣。然後運算子會將 CROSS APPLY 每個銷售訂單數據列聯結至數據表值函式所 OPENJSON 傳回的數據列。

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

Tip

當您必須展開儲存在個別欄位中的 JSON 陣列，並將其與其父數據列聯結時，您通常會使用 Transact-SQL CROSS APPLY 運算元。如需的詳細資訊 CROSS APPLY，請參閱 FROM子句。

相同的查詢可使用 OPENJSON 搭配明確定義要傳回之資料列的結構描述來重寫：

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

在此範例中，$ 路徑會參考陣列中的每個項目。若您想要明確轉換傳回的值，您可以使用這種類型的查詢。

範例 4 - 使用 CROSS APPLY 合併關聯式資料列和 JSON 項目

下列查詢會將關聯式資料列和 JSON 項目合併為在下列表格中顯示的結果。

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	postcode	lon	lat
全食品市場	17991 雷德蒙德路	西澳州 98052	47.666124	-122.10155
Sears	第 148 大道 NE	西澳州 98052	47.63024	-122.141246,17

範例 5 - 將 JSON 資料匯入 SQL Server

下列範例會將整個 JSON 物件載入至 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))

範例 6 - 使用 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

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-11-18

共用方式為

OPENJSON （Transact-SQL）

Syntax

openjson

with_clause

Arguments

jsonExpression

path

陣列元素識別

with_clause

名字

type

column_path

AS JSON

返回值

Remarks

Examples

範例 1 - 將 JSON 陣列轉換成暫存資料表

範例 2 - 合併來自兩個 JSON 物件的屬性

範例 3 - 使用 CROSS APPLY 來使用儲存在表格儲存格中的 JSON 資料聯結資料列

範例 4 - 使用 CROSS APPLY 合併關聯式資料列和 JSON 項目

範例 5 - 將 JSON 資料匯入 SQL Server

範例 6 - 使用 JSON 內容的簡單範例

相關內容

意見反應

其他資源