SQL Server 提供了許多用于處理 JSON 數(shù)據(jù)的功能，其中之一是解析 JSON 數(shù)組。在此篇文章中，我將會介紹如何在 SQL Server 中解析 JSON 數(shù)組以及一些相關(guān)的注意事項。

JSON 數(shù)組的基本概念

JSON 數(shù)組是一種存儲多個值的方式，每個值都可以是一個簡單的類型（例如字符串、數(shù)字或布爾值）或者是一個復雜的類型（例如對象或其他數(shù)組）。JSON 數(shù)組由方括號 [] 包圍，其中的值使用逗號分隔。

示例：

[ { "name": "John", "age": 30, "city": "New York" }, { "name": "Mary", "age": 25, "city": "Los Angeles" } ] 

以上是一個包含兩個對象的 JSON 數(shù)組，每個對象都有 name、age 和 city 三個屬性。

SQL Server 如何解析 JSON 數(shù)組

SQL Server 提供了 OPENJSON 函數(shù)來解析 JSON 數(shù)據(jù)。通過這個函數(shù)，你可以將 JSON 數(shù)組轉(zhuǎn)換成表格形式，每行代表一個數(shù)組元素，每列代表一個屬性。

以下是 OPENJSON 函數(shù)的基本語法：

OPENJSON(json_expression[, path]) [WITH (property_name data_type [,...])]

• json_expression：要解析的 JSON 字符串或二進制數(shù)據(jù)。
• path：指定要解析的 JSON 數(shù)組的路徑。如果不指定，默認解析整個 JSON 對象。
• property_name 和 data_type：指定要從解析結(jié)果中返回的屬性名稱和數(shù)據(jù)類型。

示例：

DECLARE @json NVARCHAR(MAX) SET @json = '[{"name": "John","age": 30,"city": "New York"},{"name": "Mary","age": 25,"city": "Los Angeles"}]' SELECT * FROM OPENJSON(@json)

以上 SQL 查詢將會返回以下結(jié)果：

+-----------+-------+-------------+
|   key     | value | type |
+-----------+-------+-------------+
|    0      |   --  |  5 (= JSON_ARRAY)|
|  [0].name | John  | 1 (= JSON_STRING)|
|  [0].age  |  30   | 2 (= JSON_INT)|
| [0].city  | New York | 1 (= JSON_STRING)|
|    1      |   --  |  5 (= JSON_ARRAY)|
|  [1].name | Mary  | 1 (= JSON_STRING)|
|  [1].age  |  25   | 2 (= JSON_INT)|
| [1].city  | Los Angeles | 1 (= JSON_STRING)|
+-----------+-------+-------------+

在上面的查詢中，我們使用了 OPENJSON 函數(shù)來解析 JSON 數(shù)組，并且沒有指定 path 參數(shù)。因此，整個 JSON 對象都被解析了。OPENJSON 函數(shù)返回了一個表格，其中每行代表一個數(shù)組元素，每列代表一個屬性。具體來說，表格包含三列：

• key：數(shù)組元素的位置。如果解析的是對象數(shù)組，則 key 的值為對象的屬性名稱。
• value：數(shù)組元素的值或?qū)傩缘闹怠?
• type：value 的數(shù)據(jù)類型。JSON 數(shù)據(jù)類型和 SQL Server 對應的數(shù)據(jù)類型可以在 MSDN 中查看。

注意事項

在使用 OPENJSON 函數(shù)時，需要注意以下幾點：

1. JSON 數(shù)組必須是有效的 JSON 格式，否則會拋出解析錯誤。
2. 如果 JSON 數(shù)組中包含了對象數(shù)組，則需要使用嵌套的 OPENJSON 函數(shù)來解析。
3. OPENJSON 函數(shù)只能返回基本數(shù)據(jù)類型（例如字符串、數(shù)字和布爾值），如果要返回復雜數(shù)據(jù)類型（例如日期時間和 GUID），需要進行一些轉(zhuǎn)換操作。
4. 如果 JSON 數(shù)組中包含了嵌套的 JSON 數(shù)組，可以使用 CROSS APPLY 子句來展開多維數(shù)組。

總結(jié)

SQL Server 提供了 OPENJSON 函數(shù)來解析 JSON 數(shù)據(jù)，可以將 JSON 數(shù)組轉(zhuǎn)換成表格形式，方便后續(xù)的數(shù)據(jù)處理。在

使用 OPENJSON 函數(shù)時，需要注意傳入的 JSON 數(shù)組必須是有效的 JSON 格式，并且如果數(shù)組中包含了對象數(shù)組，則需要使用嵌套的 OPENJSON 函數(shù)來解析。此外，OPENJSON 函數(shù)只能返回基本數(shù)據(jù)類型，如果要返回復雜數(shù)據(jù)類型，需要進行一些轉(zhuǎn)換操作。

在處理多維數(shù)組時，可以使用 CROSS APPLY 子句來展開數(shù)組。以下是一個具有嵌套數(shù)組和對象的示例：

{ "name": "John", "age": 30, "hobbies": [ { "name": "reading", "level": 3 }, { "name": "swimming", "level": 2 } ] } 

我們可以使用如下 SQL 查詢來解析該 JSON 對象：

DECLARE @json NVARCHAR(MAX) SET @json = '{"name": "John","age": 30,"hobbies": [{"name": "reading", "level": 3}, {"name": "swimming", "level": 2}]}' SELECT name, age, hobby_name, hobby_level FROM OPENJSON(@json) WITH (
    name VARCHAR(50),
    age INT,
    hobbies NVARCHAR(MAX) AS JSON
) AS person CROSS APPLY OPENJSON(person.hobbies) WITH (
 hobby_name VARCHAR(50),
 hobby_level INT );

以上 SQL 查詢將會返回以下結(jié)果：

+------+-----+------------+-------------+
| name | age | hobby_name | hobby_level |
+------+-----+------------+-------------+
| John |  30 | reading    |           3 |
| John |  30 | swimming   |           2 |
+------+-----+------------+-------------+

在查詢中，我們使用了 CROSS APPLY 子句來展開 hobbies 數(shù)組，并用嵌套的 WITH 子句來解析數(shù)組中的對象。最終得到包含兩列的結(jié)果集，其中每行代表一個 hobby 興趣。

結(jié)論

在 SQL Server 中，可以使用 OPENJSON 函數(shù)來解析 JSON 數(shù)組。通過將 JSON 數(shù)組轉(zhuǎn)換為表格形式，可以方便地進行后續(xù)的數(shù)據(jù)處理。在使用 OPENJSON 函數(shù)時，需要注意傳入的 JSON 數(shù)組必須是有效的 JSON 格式，并且如果數(shù)組中包含了對象數(shù)組，則需要使用嵌套的 OPENJSON 函數(shù)來解析。此外，在處理多維數(shù)組時，可以使用 CROSS APPLY 子句來展開數(shù)組。

推薦學習書籍

《**CDA一級教材**》適合CDA一級考生備考，也適合業(yè)務及數(shù)據(jù)分析崗位的從業(yè)者提升自我。完整電子版已上線CDA網(wǎng)校，累計已有10萬+在讀~



免費加入閱讀：https://edu.cda.cn/goods/show/3151?targetId=5147&preview=0