如何在sql server中将数据更新为json数组并选择数据作为json数组 [英] How to update data as json array and select data as json array in sql server

问题描述

我是 SQL Server 2017 的新手，用于 JSON 结果.我将 JSON 数组存储在表的一列中.我正在该表中保存 id 的数组，但我想从其他表更新其相对文本，所以请帮助我.

I am new in SQL server 2017 for JSON result. I am storing JSON array in one column in my table. I am saving id's array in that table, but i want to update its relative text from other table, so please help me in this.

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 更新主题名称，输出应该是这样的:

Now I want to update subjectname from #subjectList and output should be like this:

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

推荐答案

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

One possible approach is to parse the JSON array with IDs using OPENJSON() and default schema and then generate the JSON array with names. The OPENJSON() with default schema returns a table with columns key, value and type and the key columns holds the index of each item. Note, that the important part here is to generate the names in the same order as they exists in the IDs JSON array. You need to use an apprach based on string aggregation, because I don't think, that you can generate a JSON array with scalar values using 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"]

这篇关于如何在sql server中将数据更新为json数组并选择数据作为json数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！