使用 OPENJSON 剖析及轉換 JSON 資料
適用於： 
SQL Server 2016 (13.x) 以上版本 Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics
OPENJSON 資料列集函數可將 JSON 文字轉換成一組資料列和資料行。 一旦您使用 OPENJSON 將 JSON 集錦轉換成資料列集，即可在所傳回的資料上執行任何 SQL 查詢，或將其插入至 SQL Server 資料表。 如需在 SQL Server 資料庫引擎 中使用 JSON 資料的詳細資訊，請參閱 SQL Server 中的 JSON 資料。
OPENJSON 函數會接受單一 JSON 物件或 JSON 物件的集合，並將其轉換成一或多個資料列。 根據預設，OPENJSON 函式會傳回下列資料：
- 從 JSON 物件，此函式會傳回可在第一層找到的所有索引鍵/值組。
- 從 JSON 陣列，此函式會傳回所有陣列元素及其索引。
您可以新增選擇性 WITH 子句，以提供明確定義輸出結構的結構描述。
具有預設輸出的 OPENJSON
當您使用 OPENJSON 函式而不提供明確的結果結構描述 (也就是在 OPENJSON 之後不使用 WITH 子句) 時，此函式會傳回包含下列三個資料行的資料表：
- 輸入物件中的屬性
name(或輸入陣列中元素的索引)。 - 屬性或陣列元素的
value。 type(例如字串、數字、布林值、陣列或物件)。
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。
具有明確結構的 OPENJSON 輸出
當您使用 OPENJSON 函數的 WITH 子句指定結果的結構描述時，此函數會傳回只包含您在 WITH 子句中所定義之資料行的資料表。 在選擇性 WITH 子句中，您可以指定一組輸出資料行、其類型，以及每個輸出值的 JSON 來源屬性路徑。 OPENJSON 會逐一查看 JSON 物件的陣列、讀取為每個資料行指定之路徑上的值，並將值轉換成指定的型。
下列範例會搭配您在 子句中明確指定之輸出的 WITH 架構使用 OPENJSON。
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{
"Order": {
"Number": "SO43659",
"Date": "2024-05-31T00:00:00"
},
"AccountNumber": "AW29825",
"Item": {
"Price": 2024.9940,
"Quantity": 1
}
},
{
"Order": {
"Number": "SO43661",
"Date": "2024-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 |
2024-05-31T00:00:00 |
AW29825 |
1 |
SO43661 |
2024-06-01T00:00:00 |
AW73565 |
3 |
此函數會傳回並格式化為 JSON 陣列的元素。
針對 JSON 陣列中的每個元素，
OPENJSON會在輸出資料表中產生新的資料列。 JSON 陣列中的兩個元素會轉換成所傳回資料表中的兩個資料列。針對使用
colName type json_path語法指定的每個資料行，OPENJSON會將指定路徑上每個陣列元素中所找到的值轉換成指定的類型。 在此範例中，Date資料行的值取自路徑$.Order.Date上的每個元素，並已轉換成日期時間值。
如需詳細資訊和範例，請參閱使用 OPENJSON 與明確結構描述 (SQL Server)。
如需了解語法和使用方式，請參閱 OPENJSON。
OPENJSON 需要相容性層級 130
OPENJSON 函式僅適用於相容性等級 130 或更高。 如果您的資料庫相容性層級低於 130，SQL Server 將找不到且無法執行 OPENJSON 函式。 其他內建 JSON 函數適用於所有的相容性層級。
您可以在檢視或資料庫屬性中 sys.databases 檢查相容性層級，並使用下列命令變更資料庫的相容性層級：
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;