SQL Server FOR JSON Path Nested 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)

This should produce the output you want. Subqueries are definitely the way to go. Trying to use joins just adds complexity that isn't needed.

DECLARE @parents TABLE (ParentID int, [Name] nvarchar(max))
DECLARE @children TABLE (ChildID int, ParentID int, [Name] nvarchar(max), BirthDate datetime)

INSERT @parents (ParentID, [Name]) VALUES (1, 'Bob')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (1,1, 'Billy','4-JUL-2000')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (2,1, 'Joan','19-SEP-2005')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (3,1, 'Sam','20-JAN-2009')
INSERT @parents (ParentID, [Name]) VALUES (2, 'Joe')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (1,2, 'Billy','4-JUL-2000')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (2,2, 'Joan','19-SEP-2005')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (3,2, 'Sam','20-JAN-2009')
INSERT @parents (ParentID, [Name]) VALUES (3, 'Sarah')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (1,3, 'Billy','4-JUL-2000')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (2,3, 'Joan','19-SEP-2005')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (3,3, 'Sam','20-JAN-2009')

SELECT 
    A = [Name]
    ,child =JSON_QUERY((
           SELECT [name] = [Name],
                [date] = BirthDate
           FROM @children c
           WHERE c.ParentID = p.ParentID
           FOR JSON PATH
       ))
FROM 
    @parents p
FOR JSON PATH

I create scalar functions that generate the JSON for the subqueries. For example (assume that the table variables are actually tables):

CREATE FUNCTION dbo.ChildrenJSON(@ParentID int)
RETURNS nvarchar(max)
BEGIN
    RETURN (
           SELECT [name] = [Name],
                [date] = BirthDate
           FROM @children c
           WHERE c.ParentID = @ParentID
           FOR JSON PATH
       )

END

Your query can then look like this:

SELECT 
    A = [Name]
    ,child =JSON_QUERY(dbo.ChildrenJSON(ParentID))
FROM 
   @parents 
FOR JSON PATH

It's clean and easy to read.

This is the output:

[
  {
    "A": "Bob",
    "child": [
      {
        "name": "Billy",
        "date": "2000-07-04T00:00:00"
      },
      {
        "name": "Joan",
        "date": "2005-09-19T00:00:00"
      },
      {
        "name": "Sam",
        "date": "2009-01-20T00:00:00"
      }
    ]
  },
  {
    "A": "Joe",
    "child": [
      {
        "name": "Billy",
        "date": "2000-07-04T00:00:00"
      },
      {
        "name": "Joan",
        "date": "2005-09-19T00:00:00"
      },
      {
        "name": "Sam",
        "date": "2009-01-20T00:00:00"
      }
    ]
  },
  {
    "A": "Sarah",
    "child": [
      {
        "name": "Billy",
        "date": "2000-07-04T00:00:00"
      },
      {
        "name": "Joan",
        "date": "2005-09-19T00:00:00"
      },
      {
        "name": "Sam",
        "date": "2009-01-20T00:00:00"
      }
    ]
  }
]

Assuming this schema:

create table parent(id int primary key, name varchar(100));
create table child(id int primary key, name varchar(100), parent_id int references parent(id));

Here is a working solution - abeit more convoluted - that doesn't involve correlated subqueries and only uses FOR JSON PATH:

SELECT
  parent.name AS [name],
  child.json_agg AS [children]
FROM parent
JOIN (
  SELECT
    child.parent_code,
    JSON_QUERY(CONCAT('[', STRING_AGG(child.json, ','), ']')) AS json_agg
  FROM (
    SELECT
      child.parent_code,
      (SELECT
        child.name AS [name]
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      ) AS json
    FROM child
  ) AS child
  GROUP BY child.parent_code
) AS child
ON child.parent_code = parent.code
FOR JSON PATH

If you have an index on child.parent_id, then using a correlated subquery as suggested, or the equivalent with CROSS/OUTER APPLY might be more efficient:

SELECT
  parent.name AS [name],
  child.json AS [children]
FROM parent
OUTER APPLY (
  SELECT
    name AS [name]
  FROM child
  WHERE child.parent_id = parent.id
  FOR JSON PATH
) child(json)
FOR JSON PATH

Both queries will return :

[
    {
        "name": "foo",
        "children": [
            { "name": "bar" },
            { "name": "baz" }
        ]
    }
]