SQL Server中OPENJSON + WITH 解析JSON数据的示例_Javascript

一、概念

openjson 是 sql server（2016 及更高版本）中引入的一个表值函数，它将 json 文本转换为行和列的关系型数据结构。通过添加 with 子句，可以明确指定返回数据的结构和类型，实现 json 数据到表格数据的精确映射。

openjson 函数
openjson 函数用于将 json 文本解析为关系型数据，即将 json 数据转换为一张表。默认情况下，openjson 返回三列：
- key：json 的键值
- value：对应的值
- type：值的数据类型（例如：字符串、整数、对象、数组等标记为数字）
with 子句
使用 with 子句可以将 json 中的数据映射为指定的列，并定义其数据类型与 json 路径。这样不仅可以对 json 进行解析，还能以传统的关系型数据方式进行查询和处理。

二、语法

select column_list
from openjson(json_expression)
with (
column1 data_type '$.path1',
column2 data_type '$.path2',
...
);

说明：

json_expression：可以是一个包含 json 字符串的变量、列或直接的 json 文本。
with 子句中指定了需要映射的列名、数据类型以及 json 路径。
$.path 表示从根（$）开始的 json 路径。例如：$.id、$.customer.name 等。

这样，openjson 会把解析的结果返回为一张虚拟表，通过 select 语句可以直接查询。

三、使用示例

示例1：解析简单的 json 对象

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 数组

将整个数组转换为表行：使用 openjson 将数组中的每个元素转换为结果集中的一行。
提取数组元素的特定属性：结合 with 子句指定需要提取的属性及其数据类型。
处理嵌套数组：使用 cross apply 配合多层 openjson 调用。

关键点：

对数组元素使用 as json 选项保持 json 格式以便进一步处理
使用 cross apply 连接多个 openjson 调用来处理多层嵌套

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

输出结果

这个查询从json数组中提取基本信息并保留skills数组为json格式：

id	name	skills
1	张三	["sql", "c#", "python"]
2	李四	["java", "javascript"]

--处理用户及其标签的 json 数组
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"]}
]';
-- 提取用户基本信息（保留标签数组为 json）
select userid, username, tags
from openjson(@json)
with (
    userid int '$.userid',
    username nvarchar(50) '$.username',
    tags nvarchar(max) '$.tags' as json
) as users;
-- 展开每个用户的标签到单独的行（一对多关系）
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;

第一部分输出结果

这个查询提取用户基本信息，保留标签数组为json格式：

userid	username	tags
1	user1	["前端", "javascript", "react"]
2	user2	["后端", "python", "django"]
3	user3	["全栈", "javascript", "node.js", "mongodb"]

第二部分输出结果

这个查询使用cross apply展开每个用户的标签到单独的行，实现了一对多的关系展示：

userid	username	tag
1	user1	前端
1	user1	javascript
1	user1	react
2	user2	后端
2	user2	python
2	user2	django
3	user3	全栈
3	user3	javascript
3	user3	node.js
3	user3	mongodb

示例3：处理嵌套的 json 对象

这个例子展示了 sql server 中 json 路径表达式的使用，特别是 $.path 格式如何从根（$）开始导航嵌套的 json 结构。

重要概念解释

$ 符号：始终表示"当前上下文的根"，不一定是整个 json 文档的根
上下文切换：openjson 的第二个参数改变了解析上下文，所有 with 子句中的路径都相对于这个新上下文

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

在这个示例中：

openjson 的第二个参数 '$.employee'：
- $ 表示整个 json 文档的根
- .employee 表示从根访问名为 "employee" 的对象
- 这个参数将查询的上下文（或"基准点"）设置为 employee 对象内部
with 子句中的路径：
- '$.id' 和 '$.name' 从 employee 对象（当前上下文）直接访问属性
- '$.contact.email' 和 '$.contact.phone' 表示从当前上下文（employee 对象）开始，先访问 contact 对象，然后获取其中的 email 或 phone 属性