SQL Server:如何在一个查询中获得JSON ?

Question

我有2个查询返回json

select * from
(
SELECT Person.id, Person.name as label, 'Person' as 'group', Person.id as value
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)->Restaurant)
) 
t FOR JSON PATH,
ROOT('nodes')
--- edges
SELECT pe.id as 'from',re.id as 'to', 
(cast(pe.id as nvarchar(12))+N'=>'+cast(re.id as nvarchar(12))) as label,
'to' as arrows
FROM person pe, likes li, restaurant re
where match(pe-(li)->re)
FOR JSON PATH,ROOT('edges')

返回2个json结果

"nodes": [
{"id":1,"label":"John","group":"Person","value":1},
{"id":2,"label":"Mary","group":"Person","value":2},
{"id":3,"label":"Alice","group":"Person","value":3},
]}
{"edges":[
{"from":1,"to":1,"label":"1=>1","arrows":"to"},
{"from":2,"to":2,"label":"2=2","arrows":"to"},
]}

我想把它包装成一个返回json的函数。如何在一个查询中接收具有这样附加级别的结果:

{
"name": "Nodes", --<-- the new one!
"nodes": [
{"id":1,"label":"John","group":"Person","value":1},
{"id":2,"label":"Mary","group":"Person","value":2},
{"id":3,"label":"Alice","group":"Person","value":3},
]}
{"edges":[
{"from":1,"to":1,"label":"1=>1","arrows":"to"},
{"from":2,"to":2,"label":"2=2","arrows":"to"},
]}

Answer 1

已解决:我通过选择将它设置为nvarchar