SQL Server FOR JSON Path Nested Array

Question

We are trying to use FOR JSON Path in SQL Server 2016 for forming a Nested Array from a 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

Desired Output:

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

       ]
}]

However what we are getting is:

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

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

Nieberg

It would help to have sample input data (in the form of inserts) but basically nested JSON is generated based on the input tables. If you pre join your tables into a flat structure as a subtable or CTE and then generate JSON off that it should give a flat JSON structure. – Pustulant

Answer 1

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)

Bridgetbridgetown

thanks gordy for your reply..but I was looking for a way to do this using JOINS..as my query is much bigger and has left joins and inner joins as well. – Nieberg

Also I am not getting any values for "child" only A is present in the JSON. – Nieberg

the problem with the above is that the WHERE clause will generate the same exact output as OP got. – Capel

@Capel are you sure, just to confirm this query matches op's desired output exactly: select x.a, child = (select y.name, y.date from (values ('value', 'value'), ('value', 'value')) y(name, date) for json path) from (values ('text')) x(a) for json path – Bridgetbridgetown

Answer 2

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"
      }
    ]
  }
]

Plage

Answer 3

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" }
        ]
    }
]

Flee