SQL Server 解析/操作 Json 格式字段数据的方法实例

SQL Server 是一个功能强大的关系型数据库管理系统，它可以轻松地操作和解析 Json 格式字段数据，这对于存储和处理各种数据类型的应用程序来说非常有用。本文将介绍 SQL Server 解析/操作 Json 格式字段数据的详细攻略，其中包含两个示例说明。

Json 格式字段数据的基本概念

Json 是一种轻量级的数据交换格式，可以方便地在不同的平台和编程语言之间进行数据传输和交换。在 SQL Server 数据库中，可以使用 Json 格式字段来存储和处理复杂的数据类型，例如数组、对象和嵌套的 Json 数据。

Json 格式字段通常存储在 nvarchar(max) 或 varchar(max) 数据类型中，用于存储 Json 格式的字符串值。在解析和操作 Json 格式的数据时，可以使用 SQL Server 提供的内置函数和方法。

解析 Json 格式字段数据的方法

1. OPENJSON 函数

OPENJSON 函数是 SQL Server 2016 和更高版本中提供的一种解析 Json 格式数据的内置函数。它可以将 Json 字符串解析为一个或多个行和列的表格式。

例如，假设有一个包含以下 Json 字符串的 Test 表：

ID  JsonData
1   { "name": "Alice", "age": 25, "gender": "Female" }
2   { "name": "Bob", "age": 30, "gender": "Male" }
3   { "name": "Charlie", "age": 35, "gender": "Male", "hobby": ["reading", "music"] }

要解析 JsonData 列中的 Json 字符串并返回一个结果集，可以使用以下 SQL 语句：

SELECT ID, [key], [value]
FROM Test
CROSS APPLY OPENJSON(JsonData)

执行结果如下：

ID  key         value
1   name        Alice
1   age         25
1   gender      Female
2   name        Bob
2   age         30
2   gender      Male
3   name        Charlie
3   age         35
3   gender      Male
3   hobby       ["reading", "music"]

在这个结果集中，每个 Json 字符串的键值对都被解析为一个行，其中 [key] 列为键名，[value] 列为键值。如果值是一个嵌套的 Json 对象或数组，则可以使用嵌套的 OPENJSON 函数来进一步解析。

2. JSON_VALUE 函数

JSON_VALUE 函数是 SQL Server 2016 和更高版本中提供的一种从 Json 字符串中提取指定值或属性的内置函数。它可以使用 JsonPath 表达式获取 Json 数据中的单个值。

例如，假设要从以下 Json 字符串中获取 "name" 属性的值：

{ "name": "Alice", "age": 25, "gender": "Female" }

可以使用以下 SQL 语句：

SELECT JSON_VALUE(JsonData, '$.name') AS Name
FROM Test
WHERE ID = 1

执行结果如下：

Name
Alice

在这个示例中，JsonData 列中的 Json 字符串被解析并在 JsonPath 表达式 "$.name" 中指定了要获取的属性名称，即 "name"。

操作 Json 格式字段数据的方法

1. JSON_MODIFY 函数

JSON_MODIFY 函数是 SQL Server 2016 和更高版本中提供的一种修改 Json 格式数据的内置函数。它可以使用 JsonPath 表达式更新 Json 字符串中的属性值或添加新的属性。

例如，假设要将以下 Json 字符串的 "age" 属性值从 25 修改为 30：

{ "name": "Alice", "age": 25, "gender": "Female" }

可以使用以下 SQL 语句：

UPDATE Test
SET JsonData = JSON_MODIFY(JsonData, '$.age', 30)
WHERE ID = 1

在这个示例中，Json_MODIFY 函数使用 JsonPath 表达式 "$.age" 来更新 "age" 属性的值。最后，使用 UPDATE 语句将修改后的 Json 字符串保存回数据库。

2. JSON_QUERY 函数

JSON_QUERY 函数是 SQL Server 2016 和更高版本中提供的一种从 Json 字符串中提取指定元素的内置函数。它可以使用 JsonPath 表达式获取 Json 数据中的一个或多个元素。

例如，假设要从以下 Json 字符串中获取 "hobby" 属性的值：

{ "name": "Charlie", "age": 35, "gender": "Male", "hobby": ["reading", "music"] }

可以使用以下 SQL 语句：

SELECT JSON_QUERY(JsonData, '$.hobby') AS Hobby
FROM Test
WHERE ID = 3

执行结果如下：

Hobby
["reading", "music"]

在这个示例中，Json_QUERY 函数使用 JsonPath 表达式 "$.hobby" 来获取 "hobby" 属性的值，即一个数组类型。

结论

通过使用 SQL Server 提供的内置函数和方法，我们可以轻松地解析和操作存储在 Json 格式字段中的数据。OPENJSON、JSON_VALUE、JSON_MODIFY 和 JSON_QUERY 函数是其中最常用和最有用的函数之一，在实际应用中可以根据具体需求选择适合的函数来操作 Json 数据。