Generate Nested Nth Level JSON SQL Server Using Recursive Cte

How to generate nested nth level JSON object in T-SQL?

My apology for late posting my answer. But I really appreciate the efforts of @Iptr and @NaveenArora answer on my post. After I do some brain storming on my case I've finally found the way to do it.

Create this function:

create function [dbo].[fnUDFCreateJSON](@currentId int) 
returns varchar(max)
begin
declare @json nvarchar(max)

IF @currentId <> 0
BEGIN
set @json =
(
select [ID], [Name], CSSClass, RouteURL, json_query(dbo.fnUDFCreateJSON([ID])) as SubNavigation
from dbo.Navigations
where RootId = @currentId
for json auto
);
END
ELSE
BEGIN
set @json =
(
select [ID], [Name], CSSClass, RouteURL, '' as SubNavigation from dbo.Navigations where RootId = 0
for json auto
);
END
return @json
end

and call it by using stored procedure:

CREATE PROCEDURE [dbo].[spGetStartupNavigations]
AS
BEGIN
SELECT
(SELECT
ID, Name, CSSClass, RouteURL,
JSON_QUERY (dbo.fnUDFCreateJSON(ID)) AS SubNavigation
FROM
dbo.Navigations
WHERE
RootId = 0
FOR JSON AUTO) AS Navigation
END

That's it.

Creating nested JSON array for self-referencing table

You have to use two JSON Auto for Nested JSONs,

SELECT *,(SELECT * FROM  Assets WHERE ParentId = A.Id FOR JSON AUTO)  AS JsonData
FROM Assets A WHERE parentId IS NULL
FOR JSON AUTO

I believe this will solve your issue.

Get Hieararchical data as JSON from SQL Server

The best way I have found to do this is to use a recursive function.

You can self-join all nodes and check if they are a child using GetAncestor

CREATE FUNCTION dbo.GetJson (@parentID hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
RETURN (
SELECT
t.Id,
t.HierarchyId,
t.Name,
children = JSON_QUERY(dbo.GetJson(t.HierarchyId))
FROM Tree t
WHERE t.HierarchyId <> @parentID
AND t.HierarchyId.GetAncestor(1) = @parentID
FOR JSON PATH
);
END;

go
SELECT dbo.GetJson(hierarchyid::GetRoot());

db<>fiddle

Result

[
{
"Id": 1,
"HierarchyId": "/1/",
"Name": "MHz",
"children": [
{
"Id": 2,
"HierarchyId": "/1/2/",
"Name": "Finance",
"children": [
{
"Id": 6,
"HierarchyId": "/1/2/6/",
"Name": "Accountant"
}
]
},
{
"Id": 3,
"HierarchyId": "/1/3/",
"Name": "IT",
"children": [
{
"Id": 4,
"HierarchyId": "/1/3/4/",
"Name": "Software Development"
},
{
"Id": 5,
"HierarchyId": "/1/3/5/",
"Name": "QA"
}
]
}
]
}
]

Return JSON as a Treeview from parent-child SQL table

https://medium.com/dapper-net/one-to-many-mapping-with-dapper-55ae6a65cfd4 Try this. Looks like your trying to produce a Json object representing a one to many relationship. I would certainly make those naming conventions less confusing. Like Continent and Country for the table names.

Create nested JSON arrays using FOR JSON PATH

You can use the following query:

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name],
(
SELECT pt.pet_id AS id, pt.pet_name AS name
FROM @Pets pt WHERE pt.pet_owner=pr.person_id
FOR JSON PATH
) AS [person.pet]
FROM @Persons pr
FOR JSON PATH, ROOT('pet owners')

For more information, see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/10/09/returning-child-rows-formatted-as-json-in-sql-server-queries/

How to aggregate a table with tree-structure to a single nested JSON object?

This is tricky.
It's a recursive problem, but standard recursive CTEs are ill-equipped to deal with it, as we need to aggregate on every level and CTEs do not allow aggregation in the recursive term.

I solved it with a PL/pgSQL function:

CREATE OR REPLACE FUNCTION f_build_jsonb_tree(_type text = NULL)
RETURNS jsonb
LANGUAGE plpgsql AS
$func$
DECLARE
_nest_lvl int;

BEGIN
-- add level of nesting recursively
CREATE TEMP TABLE t ON COMMIT DROP AS
WITH RECURSIVE t AS (
SELECT *, 1 AS lvl
FROM account
WHERE "parentId" IS NULL
AND (type = _type OR _type IS NULL) -- default: whole table

UNION ALL
SELECT a.*, lvl + 1
FROM t
JOIN account a ON a."parentId" = t.id
)
TABLE t;

-- optional idx for big tables with many levels of nesting
-- CREATE INDEX ON t (lvl, id);

_nest_lvl := (SELECT max(lvl) FROM t);

-- no nesting found, return simple result
IF _nest_lvl = 1 THEN
RETURN ( -- exits functions
SELECT jsonb_agg(sub) -- AS result
FROM (
SELECT type
, jsonb_agg(sub) AS accounts
FROM (
SELECT id, code, type, "parentId", NULL AS children
FROM t
ORDER BY type, id
) sub
GROUP BY 1
) sub
);
END IF;

-- start collapsing with leaves at highest level
CREATE TEMP TABLE j ON COMMIT DROP AS
SELECT "parentId" AS id
, jsonb_agg (sub) AS children
FROM (
SELECT id, code, type, "parentId" -- type redundant?
FROM t
WHERE lvl = _nest_lvl
ORDER BY id
) sub
GROUP BY "parentId";

-- optional idx for big tables with many levels of nesting
-- CREATE INDEX ON j (id);

-- iterate all the way down to lvl 2
-- write to same table; ID is enough to identify
WHILE _nest_lvl > 2
LOOP
_nest_lvl := _nest_lvl - 1;

INSERT INTO j(id, children)
SELECT "parentId" -- AS id
, jsonb_agg(sub) -- AS children
FROM (
SELECT id, t.code, t.type, "parentId", j.children -- type redundant?
FROM t
LEFT JOIN j USING (id) -- may or may not have children
WHERE t.lvl = _nest_lvl
ORDER BY id
) sub
GROUP BY "parentId";
END LOOP;

-- nesting found, return nested result
RETURN ( -- exits functions
SELECT jsonb_agg(sub) -- AS result
FROM (
SELECT type
, jsonb_agg (sub) AS accounts
FROM (
SELECT id, code, type, "parentId", j.children
FROM t
LEFT JOIN j USING (id)
WHERE t.lvl = 1
ORDER BY type, id
) sub
GROUP BY 1
) sub
);
END
$func$;

Call (returns desired result exactly):

SELECT jsonb_pretty(f_build_jsonb_tree());

db<>fiddle here - with extended test case

I chose the key name children instead of child, as multiple may be nested.

jsonb_pretty() to prettify the display is optional.

This is assuming referential integrity; should be implemented with a FK constraint.

The solution might be simpler for your particular case, utilizing the code column - if it exhibits (undisclosed) useful properties. Like we might derive the nesting level without rCTE and added temporary table t. But I am aiming for a general solution based on ID references only.

There is a lot going on in the function. I added inline comments. Basically, it does this:

  1. Create a temporary table with added nesting level (lvl)
  2. If no nesting is found, return simple result
  3. If nesting is found, collapse to jsonb from the top nesting level down.

    Write all intermediary results to a second temp table j.
  4. Once we reach the second nesting level, return full result.

The function takes _type as parameter to return only the given type. Else, the whole table is processed.

Aside: avoid mixed-case identifiers like "parentId" in Postgres if at all possible. See:

  • Are PostgreSQL column names case-sensitive?

Related later answer using a recursive function:

  • How to turn a set of flat trees into a single tree with multiple leaves?


Related Topics



Leave a reply



Submit