SQL Server FOR JSON路径嵌套数组 [英] SQL Server FOR JSON Path Nested Array
本文介绍了SQL Server FOR JSON路径嵌套数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我们正在尝试在SQL Server 2016中使用FOR JSON Path通过SQL查询形成嵌套数组.
We are trying to use FOR JSON Path in SQL Server 2016 for forming a Nested Array from a SQL Query.
SQL查询:
SELECT A,
B.name as [child.name],
B.date as [child.date]
from Table 1 join Table 2 on Table 1.ID=Table 2.ID FOR JSON PATH
所需的输出:
[{
A:"text",
"child:"[
{"name":"value", "date":"value"},
{"name":"value", "date":"value"}
]
}]
但是我们得到的是:
[{
A:"text",
"child:" {"name":"value", "date":"value"}
},
{
A:"text",
"child":{"name":"value", "date":"value"}
}]
我们如何使用FOR JSON PATH形成嵌套的子数组.
How can we use FOR JSON PATH to form nested child array.
推荐答案
代替嵌套使用嵌套查询,例如:
instead of join use nested query, e.g.:
SELECT A
, child=(
SELECT B.name as [child.name]
, B.date as [child.date]
FROM Table 2
WHERE Table 2.ID = Table 1.ID
FOR JSON PATH
)
from Table 1 FOR JSON PATH
(问题中的查询已损坏,因此该查询也已损坏,但应该可以为您提供想法)
(the query in the question is broken af so this query is just as broken but should give you the idea)
这篇关于SQL Server FOR JSON路径嵌套数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文