Динамический ROOT с SQL Server для JSON PATH

У меня есть таблица ключевых слов, которую я хочу отобразить в объект Json:

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

который группирует каждую категорию в свой собственный объект.

Я могу создать это:

{"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"}]}
]}

с использованием:

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')

Но я хотел бы иметь возможность установить ROOT для подзапроса как самого ключевого слова. Это вообще возможно?

Желаемый результат:

{"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"}]}
]}

json sql-server

30.05.2019 22:39

Как сделать HTTP-запрос в Javascript?

В JavaScript вы можете сделать HTTP-запрос, используя объект XMLHttpRequest или более новый API fetch. Вот пример для обоих методов:

1 343

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Исходные данные:

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')
;

Я знаю, динамический SQL, однако:

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);

Потрясающий. Это ооочень близко. Он создает массив из одного объекта со свойствами. Я голосую за, потому что я определенно думаю, что это хороший подход. Я собираюсь бороться с этим завтра и посмотреть, не смогу ли я его набрать. Я сообщу об окончательном решении, когда получу его. Спасибо!

— 31.05.2019 01:40

30.05.2019 23:34

Ответ принят как подходящий

Другой возможный подход с использованием комбинации FOR JSON и манипулирования строками, но без динамического оператора:

Вход:

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

Выход:

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":"$"}]}]}

Это тоже отличный подход. Похоже, мне еще многое предстоит узнать о возможностях MSSQL. Я застрял в DB2 в течение многих лет, и я просто возвращаюсь к ней. Это хороший материал, спасибо!

— 31.05.2019 14:35

31.05.2019 08:52