CODEWITHSUNDEEP
sqlsql-serverself-referencing-table
Alex Wright
Alex Wright

Reputation: 627

Creating nested JSON array for self-referencing table

I have the following self-referencing table:

CREATE TABLE [dbo].[Assets](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Part] [nvarchar](500) NOT NULL,
    [ParentId] [int] NULL,
 CONSTRAINT [PK_Assets] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_Assets_ParentId] ON [dbo].[Assets]
(
    [ParentId] ASC
)WITH (PAD_INDEX = OFF, 
       STATISTICS_NORECOMPUTE = OFF, 
       SORT_IN_TEMPDB = OFF, 
       DROP_EXISTING = OFF, 
       ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, 
       ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
set identity_insert [dbo].[Assets] on;

insert into dbo.Assets ([id], [ParentId], [Part]) values
(1 ,null, 'HeaterAsset'),
(2 ,5,'Motor'),
(3 ,1,'Body'),
(4 ,2,'Coil'),
(5 ,1,'GearBox'),
(6 ,2,'Shaft'),
(7 ,5,'Gears'),
(8 ,null, 'FanAsset'),
(9 ,8,'Body'),
(10 ,9,'Fance'),
(11 ,8,'Motor'),
(12 ,11,'Coil'),
(13 ,11,'Shaft')

I want to create a nested JSON array output for the tree structure related to this table.

I tried the following code:

WITH cte_org AS (
    SELECT  [id], [Part], [ParentId]  FROM  [dbo].[Assets]   WHERE [ParentId] IS NULL
    UNION ALL
    SELECT  e.[id], e.[Part], e.[ParentId]  FROM [dbo].[Assets] e INNER JOIN cte_org o  ON o.[id] = e.[ParentId]
)
SELECT * FROM cte_org FOR JSON AUTO;

But the output is not nested JSON. I need to have Children instead of ParentId. For example, the desired output is:

[
    {
        id: '1', name: 'Music',
        subChild: [
            { id: '3', name: 'Gouttes.mp3' },
        ]
    },
    {
        id: '2', name: 'Videos',
        subChild: [
            { id: '4', name: 'Naturals.mp4' },
            { id: '5', name: 'Wild.mpeg' }
        ]
    },
]

How can I achieve this result?

Upvotes: 0

Views: 510

Answers (1)

Srinivasan Rajasekaran
Srinivasan Rajasekaran

Reputation: 585

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.

Upvotes: 1

Related Questions