Creating nested JSON array for self-referencing table [duplicate]

Question

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?

Use a scalar function which recurses dbfiddle.uk/…

Charlieface
– Charlieface

2022-08-30 11:09:48 +00:00
Commented Aug 30, 2022 at 11:09 — Charlieface
– Charlieface, Commented Aug 30, 2022 at 11:09

Srinivasan Rajasekaran · Accepted Answer · 2022-08-30 09:01:58Z

1

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.

answered Aug 30, 2022 at 9:01

Srinivasan Rajasekaran

5853 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Wright Over a year ago

I cannot get the last level in the JSON.

Srinivasan Rajasekaran Over a year ago

You have to use Another JSON Auto for Grouping them, You need to Group them based on the number of hierarchies.

Collectives™ on Stack Overflow

Creating nested JSON array for self-referencing table [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related