Reputation: 67
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
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
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