CODEWITHSUNDEEP
jsonsql-server
Cal Harris
Cal Harris

Reputation: 67

Dynamic ROOT With SQL Server For JSON PATH

I have a keyword table that I want to render to a Json object:

ID  Keyword     Text    Value
4   Category    A       10
5   Category    B       20
1   Season      Winter  1
2   Season      Spring  2
3   Season      Summer  3
9   Season      Fall    4
6   UnitType    Ft      Feet
7   UnitType    Set     Set
8   UnitType    $   Dollar

that groups each category into it's own object.

I've being able to create this:

{"Keywords":[
{"Keyword":"Category","Values":[{"Value":"10","Text":"A"},{"Value":"20","Text":"B"}]},
{"Keyword":"Season","Values":[{"Value":"1","Text":"Winter"},{"Value":"2","Text":"Spring"},{"Value":"3","Text":"Summer"},{"Value":"4","Text":"Fall"}]},
{"Keyword":"UnitType","Values":[{"Value":"Dollar","Text":"$"},{"Value":"Feet","Text":"Ft"},{"Value":"Set","Text":"Set"}]}
]}

using:

SELECT T.Keyword AS 'Keyword', 
(SELECT [subT].[Value] AS 'Value', [subT].[Text] AS 'Text' 
FROM tblKeywords subT WHERE subT.Keyword=T.Keyword 
ORDER BY [subT].[Value]
FOR JSON PATH) AS 'Values'
FROM tblKeywords T
GROUP BY T.Keyword
FOR JSON PATH, ROOT('Keywords')

But I'd like to be able to set the ROOT for the sub query to be keyword itself. Is this even possible?

The desired result is:

{"Keywords":[
{"Category":[{"Value":"10","Text":"A"},{"Value":"20","Text":"B"}]},
{"Season":[{"Value":"1","Text":"Winter"},{"Value":"2","Text":"Spring"},{"Value":"3","Text":"Summer"},{"Value":"4","Text":"Fall"}]},
{"UnitType":[{"Value":"Dollar","Text":"$"},{"Value":"Feet","Text":"Ft"},{"Value":"Set","Text":"Set"}]}
]}

Upvotes: 3

Views: 3173

Answers (2)

Zhorov
Zhorov

Reputation: 30023

Another possible approach using combination of FOR JSON and string manipulation, but without dynamic statement:

Input:

CREATE TABLE #tblKeywords (
   Id int, 
   [Keyword] nvarchar(50), 
   [Text] nvarchar(50), 
   [Value] nvarchar(50)
);
INSERT INTO #tblKeywords
   (ID, [Keyword], [Text], [Value])
VALUES
   (4, 'Category', 'A',      '10'),
   (5, 'Category', 'B',      '20'),
   (1, 'Season',   'Winter', '1'),
   (2, 'Season',   'Spring', '2'),
   (3, 'Season',   'Summer', '3'),
   (9, 'Season',   'Fall',   '4'),
   (6, 'UnitType', 'Ft',     'Feet'),
   (7, 'UnitType', 'Set',    'Set'),
   (8, 'UnitType', '$',      'Dollar')

T-SQL:

SELECT CONCAT(
   N'{"Keywords":[',
   STUFF(
      (
      SELECT DISTINCT CONCAT(N',{"', k.[Keyword], '":', c.[Json], N'}')
      FROM #tblKeywords k
      CROSS APPLY (
         SELECT [Value], [Text]
         FROM #tblKeywords
         WHERE [Keyword] = k.[Keyword]
         FOR JSON PATH
      ) c([Json])
      FOR XML PATH('')
      ), 1, 1, N''
   ),  
   N']}'
) AS JsonOutput

Output:

JsonOutput

{"Keywords":[{"Category":[{"Value":"10","Text":"A"},{"Value":"20","Text":"B"}]},{"Season":[{"Value":"1","Text":"Winter"},{"Value":"2","Text":"Spring"},{"Value":"3","Text":"Summer"},{"Value":"4","Text":"Fall"}]},{"UnitType":[{"Value":"Feet","Text":"Ft"},{"Value":"Set","Text":"Set"},{"Value":"Dollar","Text":"$"}]}]}

Upvotes: 2

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

Initial data:

DROP TABLE IF EXISTS #tblKeywords;
CREATE TABLE #tblKeywords (ID INT, Keyword NVARCHAR(255), [Text] NVARCHAR(255), Value NVARCHAR(255));
INSERT INTO #tblKeywords(ID,Keyword,Text,[Value])VALUES
     (4,'Category','A','10')
    ,(5,'Category','B','20')
    ,(1,'Season','Winter','1')
    ,(2,'Season','Spring','2')
    ,(3,'Season','Summer','3')
    ,(9,'Season','Fall','4')
    ,(6,'UnitType','Ft','Feet')
    ,(7,'UnitType','Set','Set')
    ,(8,'UnitType','$','Dollar')
;

I know, Dynamic SQL, however:

DECLARE @DynSql NVARCHAR(MAX) = (
    SELECT 'SELECT ' + STUFF((
            SELECT DISTINCT N',(SELECT [subT].[Value] AS [Value], [subT].[Text] AS [Text] 
            FROM #tblKeywords subT 
            WHERE subT.Keyword = ''' + t.Keyword + N''' 
            ORDER BY [subT].[Value]
            FOR JSON PATH
            ) AS [' + t.Keyword + N']' + CHAR(13) + '   '
            FROM #tblKeywords t
            FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
        + 'FOR JSON PATH, ROOT(''Keywords'')'
)
;
--PRINT @DynSql;
EXEC (@DynSql);

Upvotes: 2

Related Questions