SQL JSON:使用SQL查询FOR JSON在自定义节点下嵌套几个节点

Question

所以我写了一个查询，它将输出作为JSON对象的数组，其中一个对象在下面

{"cardType"："abc"，"createdOnDateTime"："2020-03-26"，"courseName"："course1"，"课程ID"：1，"sectionName"：1，"studentList"：[{"name"："student 1"，"nameLink"："0"}，{"name"："student 2"，"nameLink"："0"}，{"name"："student 3"，"nameLink"："0"}]}

但我希望输出为

{"cardType"："abc"，"createdOnDateTime"："2020-03-26"，"有效载荷"：{"courseName"："course1"，"课程ID"：1，"sectionName"：1，"studentList"：[{"name"："student 1"，"nameLink"："0"}，{"name"："student 2"，"nameLink"："0"}，{"name"："student 3"，"nameLink"："0"}]}}

我在Select查询的末尾使用了"For JSON Auto"短语，由于课程和学生之间存在一对多关系，学生将被格式化为数组。我想要的是，一些节点以及"studentList"节点数组应该嵌套在自定义节点"payload"下。如何使用For JSON及其相关属性在SQL查询中实现这一点？

Answer 1

根据您迄今为止给我们的信息，完全猜测您的数据和查询是什么样子的。为studentList使用for json auto的子查询，然后在外部查询中使用for json path, without_array_wrapper。

当使用for json path时，您可以通过为元素提供点分隔路径将它们嵌套在彼此内部，即：用句点(.(字符将父元素与子元素分隔开，例如以下。。。

create table dbo.Course (
cardType nvarchar(3),
createdOnDateTime date,
courseName nvarchar(20),
courseID int,
sectionName int
);
insert dbo.Course values
('abc', '2020-03-26', 'course1', 1, 1);
go
create table dbo.Student (
courseID int,
name nvarchar(20),
nameLink nvarchar(20)
);
insert dbo.Student values
(1, 'student 1', '0'),
(1, 'student 2', '0'),
(1, 'student 3', '0');
go
select
cardType,
createdOnDateTime,
[payload.courseName] = courseName,
[payload.courseID] = courseID,
[payload.sectionName] = sectionName,
[payload.studentList] = (
select name, nameLink
from dbo.Student S1
where S1.courseID = C1.courseID
for json auto
)
from dbo.Course C1
where courseID = 1
for json path, without_array_wrapper;
go

这就产生了结果。。。

{
"cardType": "abc",
"createdOnDateTime": "2020-03-26",
"payload": {
"courseName": "course1",
"courseID": 1,
"sectionName": 1,
"studentList": [
{
"name": "student 1",
"nameLink": "0"
},
{
"name": "student 2",
"nameLink": "0"
},
{
"name": "student 3",
"nameLink": "0"
}
]
}
}

Answer 2

我的问题最终得到了解决，我在youtube上发现了这段非常有用的视频，它准确地展示了我必须做的事情。点击此处查看视频。还有一件事，这个视频是一个很好的例子，但对于大尺寸数据，这种方法会使查询速度非常慢。