SQL Server中OPENJSON + WITH 解析JSON數據的示例

更新時間：2025年08月18日 14:19:03 作者：Tdm_888

文章介紹SQL Server中OPENJSON函數,通過WITH子句將JSON解析為關系型數據表,支持對象、數組及嵌套結構處理,結合CROSS APPLY實現多層展開,適用于JSON數據到表格的轉換,感興趣的朋友跟隨小編一起看看吧

一、概念

OPENJSON 是 SQL Server（2016 及更高版本）中引入的一個表值函數，它將 JSON 文本轉換為行和列的關系型數據結構。通過添加 WITH 子句，可以明確指定返回數據的結構和類型，實現 JSON 數據到表格數據的精確映射。

OPENJSON 函數
OPENJSON 函數用于將 JSON 文本解析為關系型數據，即將 JSON 數據轉換為一張表。默認情況下，OPENJSON 返回三列：
- key：JSON 的鍵值
- value：對應的值
- type：值的數據類型（例如：字符串、整數、對象、數組等標記為數字）
WITH 子句
使用 WITH 子句可以將 JSON 中的數據映射為指定的列，并定義其數據類型與 JSON 路徑。這樣不僅可以對 JSON 進行解析，還能以傳統(tǒng)的關系型數據方式進行查詢和處理。

二、語法

SELECT column_list
FROM OPENJSON(json_expression)
WITH (
column1 data_type '$.path1',
column2 data_type '$.path2',
...
);

說明：

json_expression：可以是一個包含 JSON 字符串的變量、列或直接的 JSON 文本。
WITH 子句中指定了需要映射的列名、數據類型以及 JSON 路徑。
$.path 表示從根（$）開始的 JSON 路徑。例如：$.id、$.customer.name 等。

這樣，OPENJSON 會把解析的結果返回為一張?zhí)摂M表，通過 SELECT 語句可以直接查詢。

三、使用示例

示例1：解析簡單的 JSON 對象

DECLARE @json NVARCHAR(MAX) = N'{"id": 1, "name": "張三", "age": 30, "isActive": true}';
SELECT *
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    age INT '$.age',
    isActive BIT '$.isActive'
);

示例2：處理 JSON 數組

將整個數組轉換為表行：使用 OPENJSON 將數組中的每個元素轉換為結果集中的一行。
提取數組元素的特定屬性：結合 WITH 子句指定需要提取的屬性及其數據類型。
處理嵌套數組：使用 CROSS APPLY 配合多層 OPENJSON 調用。

關鍵點：

對數組元素使用 AS JSON 選項保持 JSON 格式以便進一步處理
使用 CROSS APPLY 連接多個 OPENJSON 調用來處理多層嵌套

DECLARE @json NVARCHAR(MAX) = N'[
    {"id": 1, "name": "張三", "skills": ["SQL", "C#", "Python"]},
    {"id": 2, "name": "李四", "skills": ["Java", "JavaScript"]}
]';
SELECT id, name, skills
FROM OPENJSON(@json)
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    skills NVARCHAR(MAX) '$.skills' AS JSON
);

輸出結果

這個查詢從JSON數組中提取基本信息并保留skills數組為JSON格式：

id	name	skills
1	張三	["SQL", "C#", "Python"]
2	李四	["Java", "JavaScript"]

--處理用戶及其標簽的 JSON 數組
DECLARE @json NVARCHAR(MAX) = N'[
    {"userID": 1, "username": "user1", "tags": ["前端", "JavaScript", "React"]},
    {"userID": 2, "username": "user2", "tags": ["后端", "Python", "Django"]},
    {"userID": 3, "username": "user3", "tags": ["全棧", "JavaScript", "Node.js", "MongoDB"]}
]';
-- 提取用戶基本信息（保留標簽數組為 JSON）
SELECT userID, username, tags
FROM OPENJSON(@json)
WITH (
    userID INT '$.userID',
    username NVARCHAR(50) '$.username',
    tags NVARCHAR(MAX) '$.tags' AS JSON
) AS users;
-- 展開每個用戶的標簽到單獨的行（一對多關系）
SELECT 
    u.userID,
    u.username,
    JSON_VALUE(t.value, '$') AS tag
FROM OPENJSON(@json)
WITH (
    userID INT '$.userID',
    username NVARCHAR(50) '$.username',
    tags NVARCHAR(MAX) '$.tags' AS JSON
) AS u
CROSS APPLY OPENJSON(u.tags) AS t;

第一部分輸出結果

這個查詢提取用戶基本信息，保留標簽數組為JSON格式：

userID	username	tags
1	user1	["前端", "JavaScript", "React"]
2	user2	["后端", "Python", "Django"]
3	user3	["全棧", "JavaScript", "Node.js", "MongoDB"]

第二部分輸出結果

這個查詢使用CROSS APPLY展開每個用戶的標簽到單獨的行，實現了一對多的關系展示：

userID	username	tag
1	user1	前端
1	user1	JavaScript
1	user1	React
2	user2	后端
2	user2	Python
2	user2	Django
3	user3	全棧
3	user3	JavaScript
3	user3	Node.js
3	user3	MongoDB

示例3：處理嵌套的 JSON 對象

這個例子展示了 SQL Server 中 JSON 路徑表達式的使用，特別是 $.path 格式如何從根（$）開始導航嵌套的 JSON 結構。

重要概念解釋

$ 符號：始終表示"當前上下文的根"，不一定是整個 JSON 文檔的根
上下文切換：OPENJSON 的第二個參數改變了解析上下文，所有 WITH 子句中的路徑都相對于這個新上下文

DECLARE @json NVARCHAR(MAX) = N'{
    "employee": {
        "id": 101,
        "name": "王五",
        "contact": {
            "email": "wangwu@example.com",
            "phone": "13800138000"
        }
    }
}';
SELECT id, name, email, phone
FROM OPENJSON(@json, '$.employee')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    email NVARCHAR(100) '$.contact.email',
    phone NVARCHAR(20) '$.contact.phone'
);

在這個示例中：

OPENJSON 的第二個參數 '$.employee'：
- $ 表示整個 JSON 文檔的根
- .employee 表示從根訪問名為 "employee" 的對象
- 這個參數將查詢的上下文（或"基準點"）設置為 employee 對象內部
WITH 子句中的路徑：
- '$.id' 和 '$.name' 從 employee 對象（當前上下文）直接訪問屬性
- '$.contact.email' 和 '$.contact.phone' 表示從當前上下文（employee 對象）開始，先訪問 contact 對象，然后獲取其中的 email 或 phone 屬性