如何在SQL Server中将数据更新为JSON数组并选择数据作为JSON数组

admin

如何在SQL Server中将数据更新为JSON数组并选择数据作为JSON数组

sql

我是SQL Server
2017中针对JSON结果的新手。我将JSON数组存储在表的一列中。我将id的数组保存在该表中，但是我想从其他表中更新其相对文本，因此请帮助我。

create table #subjectList(subjectID int identity(1,1),subjectName varchar(50))
insert into #subjectList(subjectName)
select 'Math' union all
select 'English' union all
select 'Hindi' union all
select 'PC' union all
select 'Physics'

select * from #subjectList

Create table #studentList(studentID int identity(1,1), subjectName varchar(50), choseSubjectList varchar(max))
insert into #studentList(subjectName, choseSubjectList)
Select 'A','["1","2"]'

select * from #studentList

create table #studentWithSubject(studentID int,subjectName varchar(50),choseSubjectIDList varchar(max),choseSubjectNameList varchar(max))

insert into #studentWithSubject(studentID,subjectName,choseSubjectIDList)
Select a.studentID,a.studentID,a.choseSubjectList
from #studentList a

Update #studentWithSubject set choseSubjectNameList=''

select * from #studentWithSubject

这是#studentWithSubject输出

studentID   subjectName choseSubjectIDList  choseSubjectNameList
1              1         ["1","2"]          ''

现在，我要从中更新使用者名称#subjectList，输出应如下所示：

studentID   subjectName choseSubjectIDList  choseSubjectNameList
1              1         ["1","2"]          ["Math","English"]

阅读 229

2021-06-07

共1个答案

admin

一种可能的方法是使用OPENJSON()默认模式解析具有ID的JSON数组，然后生成具有名称的JSON数组。在OPENJSON()默认模式返回与列的表格key，value并type与key列保存每个项目的索引。请注意，这里重要的部分是按照与IDs
JSON数组中存在的名称相同的顺序生成名称。您需要使用基于字符串聚合的方法，因为我不认为您可以使用生成具有标量值的JSON数组FOR JSON。

表格：

create table #subjectList(subjectID int identity(1,1),subjectName varchar(50))
insert into #subjectList(subjectName)
select 'Math' union all
select 'English' union all
select 'Hindi' union all
select 'PC' union all
select 'Physics'

Create table #studentList(studentID int identity(1,1), subjectName varchar(50), choseSubjectList varchar(max))
insert into #studentList(subjectName, choseSubjectList)
Select 'A','["1","2"]' union all
Select 'B','["3","2","5"]' union all
Select 'C','["6","2"]'

create table #studentWithSubject(studentID int,subjectName varchar(50),choseSubjectIDList varchar(max),choseSubjectNameList varchar(max))
insert into #studentWithSubject(studentID,subjectName,choseSubjectIDList)
Select a.studentID,a.studentID,a.choseSubjectList
from #studentList a

陈述：

UPDATE #studentWithSubject
SET choseSubjectNameList = (
    CONCAT(
       '["',
       STUFF(
          (SELECT CONCAT('","', COALESCE(s.subjectName, ''))
          FROM OPENJSON(#studentWithSubject.choseSubjectIDList) j
          LEFT JOIN #subjectList s ON j.[value] = s.subjectID
          ORDER BY CONVERT(int, j.[key])
          FOR XML PATH('')), 1, 3, ''
       ),
       '"]'
    )   
)

结果：

studentID   subjectName choseSubjectIDList  choseSubjectNameList
1           1           ["1","2"]           ["Math","English"]
2           2           ["3","2","5"]       ["Hindi","English","Physics"]
3           3           ["6","2"]           ["","English"]

2021-06-07