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

JSON 數組的基本概念

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

示例：

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

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

SQL Server 如何解析 JSON 數組

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

以下是 OPENJSON 函數的基本語法：

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

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

示例：

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 查詢將會返回以下結果：

+-----------+-------+-------------+
|   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 函數來解析 JSON 數組，并且沒有指定 path 參數。因此，整個 JSON 對象都被解析了。OPENJSON 函數返回了一個表格，其中每行代表一個數組元素，每列代表一個屬性。具體來說，表格包含三列：

• key：數組元素的位置。如果解析的是對象數組，則 key 的值為對象的屬性名稱。
• value：數組元素的值或屬性的值。
• type：value 的數據類型。JSON 數據類型和 SQL Server 對應的數據類型可以在 MSDN 中查看。

注意事項

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

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

總結

SQL Server 提供了 OPENJSON 函數來解析 JSON 數據，可以將 JSON 數組轉換成表格形式，方便后續的數據處理。在

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

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

{ "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 查詢將會返回以下結果：

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

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

結論

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

推薦學習書籍

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



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