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