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:
- Create a temporary table with added nesting level (
lvl
) - If no nesting is found, return simple result
- If nesting is found, collapse to
jsonb
from the top nesting level down.
Write all intermediary results to a second temp tablej
. - 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
Is the 'As' Keyword Required in Oracle to Define an Alias
How to Change Date Format in Hive
Insert into Table from Comma Separated Varchar-List
How to Format a Numeric Column as Phone Number in SQL
How to Set Isolation Level on SQLcommand/Sqlconnection Initialized with No Transaction
How to Drop a Unique Constraint from Table Column
Get Size of Large Object in Postgresql Query
SQL Server: How to Get a Database Name as a Parameter in a Stored Procedure
SQL String Comparison Speed 'Like' VS 'Patindex'
Calculate Delta(Difference of Current and Previous Row) in SQL
Calculate Time Difference (Only Working Hours) in Minutes Between Two Dates
Postgres Trigger After Insert Accessing New
How to Write a Query That Does Something Similar to MySQL's Group_Concat in Oracle
Move SQL Server 2008 Database Files to a New Folder Location