How to display multiple levels of recursion in a self-referencing table?

1

I have a sample table you can see below:

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')

set identity_insert [dbo].[Assets] off;

1

I want to write a SQL Query to create a Json result containing a hierarchal top-down representation of each asset. Please help me. I'm new to SQL and I want to learn recursion by studying your code. Thanks.

2

hi hope this helps

I want to learn recursion by studying your code -- it does not work that way
Learning SQL you need to understand how it works .. then you can do anything on your own

i could give an example to you and explain how to think about it and do

;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;

image
image315×593 7.94 KB

3

Thanks. Is there any way to format the output as following example?

[
            {
                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' }
                ]
            },
]
4

by adding the for Json Auto clause

;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;
5

I know. I did it but the result is not like what I showed in the example. I need to have such a format in my Angular project.

6

Your result does not show multi-level structure:

[{"id":1,"Part":"HeaterAsset"},{"id":8,"Part":"FanAsset"},{"id":9,"Part":"Body","ParentId":8},{"id":11,"Part":"Motor","ParentId":8},{"id":12,"Part":"Coil","ParentId":11},{"id":13,"Part":"Shaft","ParentId":11},{"id":10,"Part":"Fance","ParentId":9},{"id":3,"Part":"Body","ParentId":1},{"id":5,"Part":"GearBox","ParentId":1},{"id":2,"Part":"Motor","ParentId":5},{"id":7,"Part":"Gears","ParentId":5},{"id":4,"Part":"Coil","ParentId":2},{"id":6,"Part":"Shaft","ParentId":2}]

7
8

image
image1860×683 269 KB

9

I need a nested json array. My problem is creating nested json. For example, HeaterAsset has gearbox, The gearbox has motor, the motor has shaft and coil. These are nested and I need to represent it in a nested json array. My Angular project reads json in this manner. I need to show childs instead of declaring ParentID.

10

does this look like something you want

image
image473×645 9.65 KB

11

Yes, but in nested JSON format that includes children.

12

Look at the following for nested path
result with json path.

so for following

HeaterAsset
  Motor
    Coil
      GearBox

How would you like the json to be