Nested JSON with Dynamic Query

Question

I'm looking for the best solution to create a nested JSON-string directly from (T)SQL with a dynamic SQL query.

In SQL Server 2016, it is easy to create a flat JSON string with a statement like this:

SELECT * 
FROM tblTableName 
FOR JSON AUTO

If you need a more complex nested result you can use a simple recursive routine like:

CREATE FUNCTION [dbo].[NestedJSON](@Id uniqueidentifier)  
RETURNS NVARCHAR(MAX)  
AS  
BEGIN
    DECLARE @Json NVARCHAR(MAX) = '{}';

    IF @Id Is NULL
        SET @Json =  
            (SELECT *, JSON_QUERY(dbo.NestedJSON(Id) ) AS child
            FROM    dbo.tblTableName
            WHERE   IDParent is NULL
            FOR JSON AUTO);
    ELSE
        SET @Json =  
            (SELECT *, JSON_QUERY(dbo.NestedJSON(Id) ) AS child
            FROM    dbo.tblTableName
            WHERE   IDParent = @Id
            FOR JSON AUTO);
    RETURN @Json
END

Id is the ID of each record in tblTableName

IDParent is the parent Id of the record in tblTableName

This recursive function only works if the SQL-Query is fixed.

In my situation I have many queries with a nested stucture. To support all the many nested SQL-queries I tried to modify the above NestedJSON function, but is seams that it is not allowed to use dynamic SQL in a function. I tried options like:

    IF @Id Is NULL
        Set @SQL = 'SELECT @Json=(SELECT ' + @FieldList + ' ,JSON_QUERY(dbo.MenuNested(' + @Id + ') ) AS Child FROM ' + @TheTables + ' WHERE IDParent is NULL FOR JSON AUTO)'
    ELSE
        Set @SQL = 'SELECT @Json=(SELECT ' + @FieldList + ' ,JSON_QUERY(dbo.MenuNested(' + @Id + ') ) AS Child FROM ' + @TheTables + ' WHERE IDParent = ' + @Id + ' FOR JSON AUTO)'

    Exec(@SQL)
    --or
    execute sp_executesql  @SQL;

But all modifications resulted in the same error: "Only functions and some extended stored procedures can be executed from within a function."

I call the SQL server from vb.net so I could create an additional function to tree-a-fy the nested JSON, but this is the last option for me. I think the fasted and cleanest solution is to do the complete nesting in (T)SQL.

So, Is there anyone who can help me to create a solution able to support Dynamic SQL and return a nested JSON?

Thanks any help is appreciated.

Arno

Thanks for the advice. I did not think on a remote proc yet. Is a remote procedure as fast as a function? — Arno Voerman
If your sp do the same things it will have the same execution time. The difference is how you receive the result: funcion result can be used directly in queries, sp result should be saved before you can used it — sepupic

Arno Voerman Arno Voerman · Accepted Answer · 2017-09-24T06:44:10

I finally solved the issue. For those who are interested here is the solution:

First step is to create a new table type which can be used in the recursive routine:

CREATE TYPE [dbo].[JSONCTETableType] AS TABLE(
    [Sequence] [uniqueidentifier] NULL,
    [ParentSequence] [uniqueidentifier] NULL,
    [JSON] [nvarchar](max) NULL,
    [IndentLevel] [int] NULL,
    [WBS] [nvarchar](512) NULL,
    [ChildCount] [int] NULL
)

In the second step I changed the origional recursive function to use this table definition:

-- =============================================
-- Author:      Arno Voerman
-- Create date: 2017, September
-- =============================================
CREATE FUNCTION [dbo].[TREEIFY] 
(
    @ParentId uniqueidentifier, @JSONTABLE JSONCTETableType READONLY
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Json NVARCHAR(MAX);

    If @ParentId Is NULL
        Begin
            SET @Json = (
                Select JSON + 
                    Case When ChildCount > 0 
                        THEN ',"children":[' + dbo.treeIfy(Sequence,@JSONTABLE) + ']}' 
                        ELSE ',"leaf":true}' 
                    END + ',' AS 'data()'
                FROM @JSONTABLE
                Where ParentSequence Is Null 
                FOR XML PATH('')
            )
        END
    Else
        Begin
            SET @Json = (
                Select JSON + 
                    Case When ChildCount > 0 
                        THEN ',"children":[' + dbo.treeIfy(Sequence,@JSONTABLE) + ']}' 
                        ELSE ',"leaf":true}' 
                    END + ',' AS 'data()' 
                FROM @JSONTABLE
                Where ParentSequence = @ParentId 
                FOR XML PATH('')
            )
        End
    RETURN @Json
END

To get the nested JSON-result from the query the query-result must be transformed into JSONCTETableType. This is done in the following CTE which also generates the JSON and some extra data which my program needs:

DECLARE @JSONTT as JSONCTETableType;
WITH JSONCTETable AS (
    SELECT  Sequence, IDParentSequence As ParentSequence,
            (SELECT * FROM YourTable Where Sequence=anchor.Sequence FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) As JSON,
            0 as IndentLevel, 
            Cast(ROW_NUMBER() OVER(ORDER BY mnu_Order ASC) As NVARCHAR(512)) AS WBS
    FROM    YourTable AS anchor
    WHERE   IDParentSequence is null
UNION   ALL -------------------------------------------------------------------

SELECT  recur.Sequence, recur.IDParentSequence As ParentSequence,
        (SELECT * FROM YourTable Where Sequence=recur.Sequence FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) As JSON,
        cte.IndentLevel + 1, 
        Cast(cte.WBS + '.' + Cast(ROW_NUMBER() OVER(ORDER BY mnu_Order ASC) As NVARCHAR(10)) AS NVARCHAR(512)) AS WBS
FROM    YourTable AS recur
        INNER JOIN JSONCTETable AS cte ON cte.Sequence = recur.IDParentSequence

)INSERT INTO @JSONTT (Sequence, ParentSequence, JSON, IndentLevel, WBS, ChildCount) 
SELECT  *, (Select count(*) From JSONCTETable tmpCTE Where tmpCTE.ParentSequence=JSONCTETable.Sequence) as ChildCount
FROM    JSONCTETable
ORDER BY WBS;

--Remove last character ("}") of all JSON. Needed to simplify the add "CHILD"
Update @JSONTT SET JSON = stuff(JSON,len(JSON),1,'');

The last step is simple:

Select '[' + dbo.treeIfy(Null,@JSONTT) + ']' as data, (Select count(Sequence) From @JSONTT) as totRows;

Good luck!

Arno

Nested JSON with Dynamic Query

2 Answers