一、概念

OPENJSON 是 SQL Server（2016 及更高版本） 中引入的一個(gè)表值函數(shù)，它將 JSON 文本轉(zhuǎn)換為行和列的關(guān)系型數(shù)據(jù)結(jié)構(gòu)。通過(guò)添加 WITH 子句，可以明確指定返回?cái)?shù)據(jù)的結(jié)構(gòu)和類型，實(shí)現(xiàn) JSON 數(shù)據(jù)到表格數(shù)據(jù)的精確映射。

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

二、語(yǔ)法

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

說(shuō)明：

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

這樣，OPENJSON 會(huì)把解析的結(jié)果返回為一張?zhí)摂M表，通過(guò) SELECT 語(yǔ)句可以直接查詢。

三、使用示例

示例1：解析簡(jiǎn)單的 JSON 對(duì)象

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 數(shù)組

• 將整個(gè)數(shù)組轉(zhuǎn)換為表行：使用 OPENJSON 將數(shù)組中的每個(gè)元素轉(zhuǎn)換為結(jié)果集中的一行。
• 提取數(shù)組元素的特定屬性：結(jié)合 WITH 子句指定需要提取的屬性及其數(shù)據(jù)類型。
• 處理嵌套數(shù)組：使用 CROSS APPLY 配合多層 OPENJSON 調(diào)用。

關(guān)鍵點(diǎn)：

• 對(duì)數(shù)組元素使用 AS JSON 選項(xiàng)保持 JSON 格式以便進(jìn)一步處理
• 使用 CROSS APPLY 連接多個(gè) OPENJSON 調(diào)用來(lái)處理多層嵌套

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

輸出結(jié)果

這個(gè)查詢從JSON數(shù)組中提取基本信息并保留skills數(shù)組為JSON格式：

--處理用戶及其標(biāo)簽的 JSON 數(shù)組
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"]}
]';
-- 提取用戶基本信息（保留標(biāo)簽數(shù)組為 JSON）
SELECT userID, username, tags
FROM OPENJSON(@json)
WITH (
    userID INT '$.userID',
    username NVARCHAR(50) '$.username',
    tags NVARCHAR(MAX) '$.tags' AS JSON
) AS users;
-- 展開(kāi)每個(gè)用戶的標(biāo)簽到單獨(dú)的行（一對(duì)多關(guān)系）
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;

第一部分輸出結(jié)果

這個(gè)查詢提取用戶基本信息，保留標(biāo)簽數(shù)組為JSON格式：

第二部分輸出結(jié)果

這個(gè)查詢使用CROSS APPLY展開(kāi)每個(gè)用戶的標(biāo)簽到單獨(dú)的行，實(shí)現(xiàn)了一對(duì)多的關(guān)系展示：

示例3：處理嵌套的 JSON 對(duì)象

這個(gè)例子展示了 SQL Server 中 JSON 路徑表達(dá)式的使用，特別是 $.path 格式如何從根（$）開(kāi)始導(dǎo)航嵌套的 JSON 結(jié)構(gòu)。

重要概念解釋

• $ 符號(hào)：始終表示"當(dāng)前上下文的根"，不一定是整個(gè) JSON 文檔的根
• 上下文切換：OPENJSON 的第二個(gè)參數(shù)改變了解析上下文，所有 WITH 子句中的路徑都相對(duì)于這個(gè)新上下文

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

在這個(gè)示例中：

1. OPENJSON 的第二個(gè)參數(shù) '$.employee'：
   • $ 表示整個(gè) JSON 文檔的根
   • .employee 表示從根訪問(wèn)名為 "employee" 的對(duì)象
   • 這個(gè)參數(shù)將查詢的上下文（或"基準(zhǔn)點(diǎn)"）設(shè)置為 employee 對(duì)象內(nèi)部
2. WITH 子句中的路徑：
   • '$.id' 和 '$.name' 從 employee 對(duì)象（當(dāng)前上下文）直接訪問(wèn)屬性
   • '$.contact.email' 和 '$.contact.phone' 表示從當(dāng)前上下文（employee 對(duì)象）開(kāi)始，先訪問(wèn) contact 對(duì)象，然后獲取其中的 email 或 phone 屬性

到此這篇關(guān)于SQL Server中OPENJSON + WITH 來(lái)解析JSON的文章就介紹到這了,更多相關(guān)sql openjson解析json內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論