SQL Server FOR JSON路径嵌套数组 [英] SQL Server FOR JSON Path Nested Array

问题描述

我们正在尝试在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屋！