SQL Server 2016 存储过程中的串联 OPENJSON [英] A concatenation OPENJSON in a SQL Server 2016 stored procedure
本文介绍了SQL Server 2016 存储过程中的串联 OPENJSON的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要组合特定 UID 的所有作者.基本字段在另一篇文章的代码中起作用.
I need to combine all of the authors for a particular UID. The basic fields are working from the code at another post.
DECLARE @json NVARCHAR(MAX)
SET @json = '{
"header": {
"type": "esummary",
"version": "0.3"
},
"result": {
"uids": [
"17784783",
"19505939",
"30166592"
],
"17784783": {
"uid": "17784783",
"pubdate": "2007 Aug",
"epubdate": "2007 Jul 20",
"source": "PLoS Comput Biol",
"sortpubdate": "2007/08/01 00:00",
"authors": [
{
"name": "Yu Y",
"authtype": "Author",
"clusterid": ""
},
{
"name": "Wang G",
"authtype": "Author",
"clusterid": ""
},
{
"name": "Simha R",
"authtype": "Author",
"clusterid": ""
}
]
,
"19505939": {
"uid": "19505939",
"pubdate": "2009 Aug 1",
"epubdate": "2009 Jun 8",
"source": "Bioinformatics",
"sortpubdate": "2009/08/01 00:00"
},
"authors": [
{
"name": "Zang C",
"authtype": "Author",
"clusterid": ""
}],
"30166592": {
"uid": "30166592",
"pubdate": "2019 Jan",
"epubdate": "2018 Aug 30",
"source": "Oncogene",
"sortpubdate": "2019/01/01 00:00",
"authors": [
{
"name": "Sun J",
"authtype": "Author",
"clusterid": ""
},
{
"name": "Cai X",
"authtype": "Author",
"clusterid": ""
}],
}
}
}'
我想结束
uid sortpubdate epubdate Authors
-----------------------------------------------------------------------
17784783 2007/08/01 00:00 2007 Jul 20 Yu Y,Wang G,Simha R
19505939 2009/08/01 00:00 2009 Jun 8 Simha R
30166592 2019/01/01 00:00 2018 Aug 30 Sun J, Cai, X
我在 从 Zohar Peled 那里得到了很好的回答SQL Server 2016 中存储过程中的 OPENJSON 语法 对第一部分有所帮助，现在我需要看看是否可以完成.
I got a great answer from Zohar Peled at OPENJSON syntax in a stored procedure in SQL Server 2016 that help with the first part now I need to see if I can finish this up.
SELECT [uid], [sortpubdate], [epubdate]
FROM OPENJSON(@json, N'$.result') AS items
CROSS APPLY
-- parse each object in the array
OPENJSON(items.[value])
WITH(
[uid] nvarchar(max) N'$.uid' ,
[sortpubdate] nvarchar(max) N'$.sortpubdate',
[epubdate] nvarchar(max) N'$.epubdate'
) As content
WHERE [key] <> 'uids' -- Get only the relevant content
推荐答案
你贴的 json 有点乱，所以我编辑了.
The json you've posted is a bit messy, so I've edited it.
下一个查询应该使用公共表表达式以及stuff 和for xml 的组合为您提供包括作者姓名在内的结果.
This next query should get you the results including the author names using a common table expression and a combination of stuff and for xml.
固定的json:
DECLARE @json NVARCHAR(MAX) =
'{
"header": {
"type": "esummary",
"version": "0.3"
},
"result": {
"17784783": {
"uid": "17784783",
"pubdate": "2007 Aug",
"epubdate": "2007 Jul 20",
"source": "PLoS Comput Biol",
"sortpubdate": "2007/08/01 00:00",
"authors": [
{
"name": "Yu Y",
"authtype": "Author",
"clusterid": ""
},
{
"name": "Wang G",
"authtype": "Author",
"clusterid": ""
},
{
"name": "Simha R",
"authtype": "Author",
"clusterid": ""
}
]
},
"19505939": {
"uid": "19505939",
"pubdate": "2009 Aug 1",
"epubdate": "2009 Jun 8",
"source": "Bioinformatics",
"sortpubdate": "2009/08/01 00:00",
"authors": [
{
"name": "Zang C",
"authtype": "Author",
"clusterid": ""
}
]
},
"30166592": {
"uid": "30166592",
"pubdate": "2019 Jan",
"epubdate": "2018 Aug 30",
"source": "Oncogene",
"sortpubdate": "2019/01/01 00:00",
"authors": [
{
"name": "Sun J",
"authtype": "Author",
"clusterid": ""
},
{
"name": "Cai X",
"authtype": "Author",
"clusterid": ""
}
]
},
"uids": [
"17784783",
"19505939",
"30166592"
]
}
}';
使用公用表表达式从 json 中提取值:
Use a common table expression to extract the values from the json:
WITH CTE AS
(
SELECT [uid],
[sortpubdate],
[epubdate],
[name]
FROM OPENJSON(@json, N'$.result') AS items
CROSS APPLY
-- parse each object in the array
OPENJSON(items.[value])
WITH(
[uid] nvarchar(max) N'$.uid' ,
[sortpubdate] nvarchar(max) N'$.sortpubdate',
[epubdate] nvarchar(max) N'$.epubdate',
-- Note the AS JSON on the next row - will not work without it!
[authors] nvarchar(max) N'$.authors' AS JSON
) As content
CROSS APPLY
OPENJSON([authors])
WITH ([name] nvarchar(max) N'$.name')
As authorsNames
WHERE items.[key] <> 'uids' -- Get only the relevant content
)
并用stuff和for xml查询cte:
SELECT DISTINCT [uid],
[sortpubdate],
[epubdate],
STUFF((
SELECT ',' + [name]
FROM CTE As t1
WHERE t1.[uid] = t0.[uid]
FOR XML PATH('')
), 1, 1, '') As authors
FROM CTE As t0
结果:
uid sortpubdate epubdate authors
17784783 2007/08/01 00:00 2007 Jul 20 Yu Y,Wang G,Simha R
19505939 2009/08/01 00:00 2009 Jun 8 Zang C
30166592 2019/01/01 00:00 2018 Aug 30 Sun J,Cai X
这篇关于SQL Server 2016 存储过程中的串联 OPENJSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
