How to display output in tree-type JSON SQL SERVER

Question

Good day. I am trying to show a query in JSON but in tree type. As follows: Desired output.

enter image description here

I have the following query where I generate the head node and the key or link, this is my query.

 Select --value, 
 --c.IDEMPRESA,
 rtrim(c.JERARQUIA) as JERARQUIA,
 RTRIM(c.IDCONSUMIDOR) as IDCONSUMIDOR, 
 RTRIM(c.DESCRIPCION) DESCRIPCION,
 [KEY]
 
 from consumidor c

I attach the result of this query https://dbfiddle.uk/Vi3hP6k6

I have found that to output in JSON format in sql server, the following statement is used.

FOR JSON PATH, INCLUDE_NULL_VALUES

But I don't know where this line of code should go. Any suggestions on how I can have the output in tree-type JSON?

• Charlieface: And where is the sample data for this query?
• kenny: here dbfiddle.uk/Vi3hP6k6
• kenny: update the query, in the table the relationship field is [key], what I want to obtain from that table is, for example, FATHER - SON - SUB CHILD type tree, I understand that for json type outputs it is the following command FOR JSON PATH , INCLUDE_NULL_VALUES

Answer 1

Unfortunately it's very difficult to recursively aggregate in SQL Server.

The easiest method is to just use a scalar function which recurses.

CREATE OR ALTER FUNCTION dbo.GetJson (@ParentKey varchar(512))
RETURNS nvarchar(max)
AS BEGIN
RETURN ISNULL((
 Select --value, 
 --c.IDEMPRESA,
 rtrim(c.JERARQUIA) as JERARQUIA,
 RTRIM(c.IDCONSUMIDOR) as IDCONSUMIDOR, 
 RTRIM(c.DESCRIPCION) DESCRIPCION,
 [KEY],
 items = JSON_QUERY(dbo.GetJson(c.JERARQUIA))
 from dbo.consumidor c
 WHERE [KEY] = @ParentKey
 FOR JSON PATH, INCLUDE_NULL_VALUES
), N'[]');
END;

Then just begin with the parent or root ID (for some reason it's a space in your case).

SELECT dbo.GetJson(' ');

db<>fiddle

• kenny: Okay, I just checked and it meets what is required, thanks with this example it helps me continue investigating.