SQL Server 2016 - JSON format query results

Question

I'm using SQL server 2016 have this query:

SELECT TOP (100) 
        brm.practice,
        (select count(*) from _rl_metadata where practice=brm.practice) As TotalPractice,
        brm.primary_subject_area,
        (select count(*) from _rl_metadata where primary_subject_area=brm.primary_subject_area) As TotalSubject,
        brm.content_id,
        brm.content_title
  FROM [_bersin_rl_metadata] AS brm 
    Where brm.is_archive <> 1 and brm.is_published_to_site = 1 

enter code here

from this table:

CREATE TABLE [dbo].[_rl_metadata](
    [content_id] [bigint] NOT NULL,
    [content_title] [varchar](200) NULL,
    [publish_date] [datetime] NULL,
    [practice] [nvarchar](50) NULL,
    [primary_subject_area] [nvarchar](50) NULL
)

that returns these results:

I want to display these results in a hierarchical JSON format (I want to use it in a radial d3 chart like this: https://bl.ocks.org/mbostock/4348373) grouped by the number of assets in Practice, then Subject, and showing properties of each asset (e.g. Title, ID, Publish Date) like this:

{
    "name": "Research",
    "children": [{
        "name": "Human Resources",
        "size": 290,
        "children": [{
                "name": "HR & Talent Analytics",
                "size": 75,
                "children": [{ "name": "People Analytics Framework" }, { "name": "Data, Big Data and You" }, ...]
            },
            {
                "name": "HR Org. & Governance",
                "size": 52,
                "children": [{ "name": "Structuring the HR Business" }, { "name": "Relationship Management" }, ...]
            },... 
        ]
    }]
}

What's the best way to get this structure using SQL Server 2016?

Answer 1

Try following solution:

DECLARE @SourceTable TABLE (
    Level1_Name NVARCHAR(50) NOT NULL,
    Level1_Size INT NOT NULL,
    Level2_Name NVARCHAR(50) NOT NULL,
    Level2_Size INT NOT NULL,
    Content     NVARCHAR(100) NOT NULL
);
INSERT  @SourceTable
VALUES  
('Leadership', 270, 'Solutions', 70,    'Book #1'),
('Leadership', 270, 'Solutions', 70,    'Book #2'),
('Leadership', 270, 'Strategy', 121,    'Book #3'), 
('Leadership', 270, 'Strategy', 121,    'Book #4'), 
('Leadership', 270, 'Strategy', 121,    'Book #5'), 
('Leadership', 270, 'Development', 10,  'Book #6'), 
('Coco Jambo', 111, 'Solutions', 111, 'Book #111');

SELECT 
    name    = 'Root object',
    children= (
        SELECT  DISTINCT 
            name    = lvl1.Level1_Name, 
            size    = lvl1.Level1_Size,
            children= (
                SELECT  DISTINCT 
                    name    = lvl2.Level2_Name, 
                    size    = lvl2.Level2_Size,
                    children= (
                        SELECT  DISTINCT 
                            name    = lvl3.Content 
                        FROM    @SourceTable lvl3
                        WHERE   lvl2.Level1_Name = lvl1.Level1_Name 
                        AND     lvl3.Level2_Name = lvl2.Level2_Name
                        FOR JSON PATH
                    )
                FROM    @SourceTable lvl2
                WHERE   lvl2.Level1_Name = lvl1.Level1_Name
                FOR JSON PATH
            )
        FROM    @SourceTable lvl1
        FOR JSON PATH
    )
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Results:

{
  "name": "Root object",
  "children": [
    {
      "name": "Leadership",
      "size": 270,
      "children": [
        {
          "name": "Development",
          "size": 10,
          "children": [
            {
              "name": "Book #6"
            }
          ]
        },
        {
          "name": "Solutions",
          "size": 70,
          "children": [
            {
              "name": "Book #1"
            },
            {
              "name": "Book #111"
            },
            {
              "name": "Book #2"
            }
          ]
        },
 ...

Demo