SQL Server FOR JSON Path Nested Array

Question

We are trying to use FOR JSON Path in SQL Server 2016 to form a nested array from an SQL query.

SQL query:

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

Output Required:

[{
A:"text",
   "child:"[
         {"name":"value", "date":"value"},
         {"name":"value", "date":"value"}

       ]
}]

However, we get:

 [{
    A:"text",
    "child:" {"name":"value", "date":"value"}
  },
{
   A:"text",
  "child":{"name":"value", "date":"value"}
}]

How can we use FOR JSON PATH to form a nested child array.

Answer 1

use a nested query instead of a connection, for example:

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 so that this query is also broken, but should give you an idea)