Hierarchical JSON output from table

Question

I've got this table structure

| User | Type    | Data |
|------|---------|------|
| 1    | "T1"    | "A"  |
| 1    | "T1"    | "B"  |
| 1    | "T2"    | "C"  |
| 2    | "T1"    | "D"  |

I want to get a hierarchical JSON string returned from my query

{
  "1": {
    "T1": [
      "A",
      "B"
    ],
    "T2": [
      "C"
    ]
  },
  "2": {
    "T1": [
      "D"
    ]
  }
}

So one entry for each User with a sub-entry for each Type and then a sub-entry for each Data

All I'm finding is the FOR JSON PATH, ROOT ('x') or AUTO statement but nothing that would make this hierarchical. Is this even possible out of the box? I couldn't find anything so I've experimented with (recursive) CTE but didn't get very far. I'd much appreciate if someone could just point me in the right direction.

Zhorov · Accepted Answer · 2019-09-25 17:00:11Z

I'm not sure that you can create JSON with variable key names using FOR JSON AUTO and FOR JSON PATH. I suggest the following solutions:

using FOR XML PATH to generate JSON with string manipulations
using STRING_AGG() to generate JSON with string manipulations for SQL Server 2017+
using STRING_AGG() and JSON_MODIFY() for SQL Server 2017+

Table:

CREATE TABLE #InputData (
   [User] int,
   [Type] varchar(2),
   [Data] varchar(1)
)
INSERT INTO #InputData 
   ([User], [Type], [Data])
VALUES
   (1, 'T1', 'A'),
   (1, 'T1', 'B'),
   (1, 'T2', 'C'),
   (2, 'T1', 'D')

Statement using FOR XML PATH:

;WITH SecondLevelCTE AS (
   SELECT 
      d.[User],
      d.[Type],
      Json1 = CONCAT(
         '[', 
         STUFF(
         (
         SELECT CONCAT(',"', [Data], '"')
         FROM #InputData 
         WHERE [User] = d.[User] AND [Type] = d.[Type]
         FOR XML PATH('')
         ), 1, 1, ''),
         ']')
   FROM #InputData d
   GROUP BY d.[User], d.[Type]
), FirstLevelCTE AS (
   SELECT 
      d.[User],
      Json2 = CONCAT(
         '{',
         STUFF(
         (
         SELECT CONCAT(',"', [Type], '":', [Json1])
         FROM SecondLevelCTE 
         WHERE [User] = d.[User]
         FOR XML PATH('')
         ), 1, 1, ''),
         '}'
      )
   FROM SecondLevelCTE d
   GROUP BY d.[User]
)
SELECT CONCAT(
   '{',
   STUFF(
   (
   SELECT CONCAT(',"', [User], '":', Json2)
   FROM FirstLevelCTE
   FOR XML PATH('')
   ), 1, 1, '')   ,
   '}'
)

Statement using STRING_AGG():

;WITH SecondLevelCTE AS (
   SELECT 
      d.[User],
      d.[Type],
      Json1 = (
         SELECT CONCAT('["', STRING_AGG([Data], '","'), '"]')
         FROM #InputData 
         WHERE [User] = d.[User] AND [Type] = d.[Type]
      )
   FROM #InputData d
   GROUP BY d.[User], d.[Type]
), FirstLevelCTE AS (
   SELECT 
      d.[User],
      Json2 = (
         SELECT STRING_AGG(CONCAT('"', [Type], '":', [Json1]), ',')
         FROM SecondLevelCTE
         WHERE [User] = d.[User]
      )
   FROM SecondLevelCTE d
   GROUP BY d.[User]
)
SELECT CONCAT('{', STRING_AGG(CONCAT('"', [User], '":{', Json2, '}'), ','), '}')
FROM FirstLevelCTE

Statement using STRING_AGG() and JSON_MODIFY():

DECLARE @json nvarchar(max) = N'{}'
SELECT 
   @json = JSON_MODIFY(
      CASE 
         WHEN JSON_QUERY(@json, CONCAT('$."', [User] , '"')) IS NULL THEN JSON_MODIFY(@json, CONCAT('$."', [User] , '"'), JSON_QUERY('{}'))
         ELSE @json
      END,
      CONCAT('$."', [User] , '".', [Type]), 
      JSON_QUERY(Json)
   )
FROM (
   SELECT 
      d.[User],
      d.[Type],
      Json = (
         SELECT CONCAT('["', STRING_AGG([Data], '","'), '"]')
         FROM #InputData 
         WHERE [User] = d.[User] AND [Type] = d.[Type]
      )
   FROM #InputData d
   GROUP BY d.[User], d.[Type]
) t

Output:

{"1":{"T1":["A","B"],"T2":["C"]},"2":{"T1":["D"]}}

Great stuff. I'm limited to SQL2016 so the top one works for me. I've done more reading on it and there appears to be no other way. It seems like it's not a too well performing way of doing it, though, with the string manipulations etc...

uberbloke · Accepted Answer · 2019-09-25 07:38:39Z

2

This isn't exactly what you want (I'm not great with FOR JSON) but it does get you close to the shape you need until something better comes along... (https://jsonformatter.org/json-parser/974b6b)

use tempdb
GO

drop table if exists users
create table users (
    [user] integer
    , [type] char(2)
    , [data] char(1)
)


insert into users 
values (1, 'T1', 'A')
    , (1, 'T1', 'B')
    , (1, 'T2', 'C')
    , (2, 'T1', 'D')

select DISTINCT ONE.[user], two.[type], three.[data] 
from users AS ONE
inner join users two
    on one.[user] = two.[user]
inner join users three
    on one.[user] = three.[user]
    and two.[type] = three.[type]
for JSON AUTO

answered Sep 25, 2019 at 7:38

uberbloke

1166 bronze badges

1 Comment

AskPete Over a year ago

Thanks. Yes, it seems like there is no way around string manipulation to achieve this. A shame as I need it to perform really well.

Collectives™ on Stack Overflow

Hierarchical JSON output from table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related