我曾试图从Akk服务器查询中收回以下结果:
{
"data":[
{"id":3,"type":1,"job":1},
{"id":4,"type":2,"job":34},
],
"collections": {
"jobs":[
{"key":1,"label":"Fenchurch"},
{"key":34,"label":"Raggle"}
],
"users":[
{"id":5,"label":"Bob"},
{"id":20,"label":"Jeff"}
]
}
}
我的最近两件尝试使我感到::
{
"data":[
{"id":3,"type":1,"job":1},
{"id":4,"type":2,"job":34},
],
"collections":[{
"jobs":[
{"key":1,"label":"Fenchurch"},
{"key":34,"label":"Raggle"}
],
"users":[
{"id":5,"label":"Bob"},
{"id":20,"label":"Jeff"}
]
}]
}
通知 Collections”:[{和封闭>> 仅应为 Collections>:{<><>>>
我用以下文件来说明这一结果:
SELECT
(
SELECT
data = (
SELECT TOP 2 *
FROM Event
FOR JSON PATH
),
(
SELECT
(
SELECT TOP 2 id AS key , job_number AS label
FROM [Job]
ORDER BY job_number DESC
FOR JSON PATH
) AS jobs ,
(
SELECT TOP 2 id, full_name AS label
FROM [User]
ORDER BY full_name
FOR JSON PATH
) AS users
FOR JSON PATH
) AS collections
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS result
我的另一项最接近的尝试是利用CONCAT_WS(CONCAT)给我如下:
{
"data":[
{"id":3,"type":1,"job":1},
{"id":4,"type":2,"job":34},
],
"collections":"
{
"jobs":[
{"key":1,"label":"Fenchurch"},
{"key":34,"label":"Raggle"}
]
},
{
"users":[
{"id":5,"label":"Bob"},
{"id":20,"label":"Jeff"}
]
}"
}
Which enclosed "jobs" and "users" in individual { } rather both being within one set.
实现这一目标的基点如下:
SELECT
(
SELECT
data = (
SELECT TOP 2 *
FROM Event
FOR JSON PATH
),
(
SELECT CONCAT_WS( , ,
(
SELECT TOP 2 id AS key , job_number AS label
FROM [Job]
ORDER BY job_number DESC
FOR JSON PATH, ROOT( jobs )
),
(
SELECT TOP 2 id, full_name AS label
FROM [User]
ORDER BY full_name
FOR JSON PATH, ROOT( users )
)
)
) AS collections FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS result
我开始考虑把这个问题分成多个问题,然后建造我自己的“智者”物体,但我很想到的是,我需要社区帮助。
非常赞赏这方面的帮助。
事先感谢你。