Преобразование результата в двояковложенный формат JSON

Question

Я пытаюсь преобразовать результаты SQL Server в формат JSON с двойной вложенностью.

Исходная таблица SQL Server:

Преобразуйте в двойной JSON, как показано здесь:

      [
    [
    {"key":"ID","value":"123"},
    {"key":"Name","value":"abc"},
    {"key":"Program","value":"qew"},
    {"key":"Type","value":"tyu"},
    {"key":"Section","value":"dd"},
    {"key":"Director","value":"ghghjg"},
    {"key":"Project","value":"hkhjk"},
    {"key":"Sr Manager","value":"fghfgf"},
    {"key":"PCM","value":"gnhghj"},
    {"key":"Contractor","value":"gghgh"},
    {"key":"Cost Client","value":"gghhg"}
    ],
    [
    {"key":"ID","value":"456"},
    {"key":"Name","value":"yui"},
    {"key":"Program","value":"gdffgf"},
    {"key":"Type","value":"ghgfjhjhj"},
    {"key":"Section","value":"jkjlkll"},
    {"key":"Director","value":"uiop"},
    {"key":"Project","value":"rtyuui"},
    {"key":"Sr Manager","value":"rfv"},
    {"key":"PCM","value":"ujmk"},
    {"key":"Contractor","value":"rfvtg"},
    {"key":"Cost Client","value":"efgg"}
    ]
]

Любая помощь будет принята с благодарностью.

Изменить: я начал с этого, переписав «FOR JSON AUTO», чтобы я мог как-то добавить текст «Ключ» «Значение». Но поскольку в моей таблице есть пробел в имени столбца, FOR XML PATH('') дает недопустимый идентификатор XML, как того требует ошибка FOR XML. тогда я подумал о помощи сообщества.

      Create PROCEDURE [dbo].[GetSQLtoJSON] @TableName VARCHAR(255)
    AS
    BEGIN
        IF OBJECT_ID(@TableName) IS NULL
            BEGIN
                SELECT Json = '';
                RETURN
            END;
    
    
        DECLARE @SQL NVARCHAR(MAX) = N'SELECT * INTO ##T ' + 
                                    'FROM ' + @TableName;
    
        EXECUTE SP_EXECUTESQL @SQL;
    
        DECLARE @X NVARCHAR(MAX) = '[' + (SELECT * FROM ##T FOR XML PATH('')) + ']';
    
    
        SELECT  @X = REPLACE(@X, '<' + Name + '>', 
                        CASE WHEN ROW_NUMBER() OVER(ORDER BY Column_ID) = 1 THEN '{'
                             ELSE '' END + Name + ':'),
                @X = REPLACE(@X, '</' + Name + '>', ','),
                @X = REPLACE(@X, ',{', '}, {'),
                @X = REPLACE(@X, ',]', '}]')
        FROM    sys.columns
        WHERE   [Object_ID] = OBJECT_ID(@TableName)
        ORDER BY Column_ID;
    
        DROP TABLE ##T;
    
        SELECT  Json = @X;
    
    END

Образец данных:

      CREATE TABLE [dbo].[Test1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Col1] [int] NOT NULL,
    [Col 2] varchar(50)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Test1] ON 
GO
INSERT [dbo].[Test1] ([ID], [Col1], [Col 2]) VALUES (1, 0,'ABCD')
GO
INSERT [dbo].[Test1] ([ID], [Col1] ,[Col 2]) VALUES (2, 1, 'POIU')

GO
SET IDENTITY_INSERT [dbo].[Test1] OFF
GO

Answer 1

Вы можете использовать следующий код:

• Внутри APPLY, развернуть столбцы как пары ключ / значение ...
• ... и агрегировать, используя FOR JSON PATH
• Использовать STRING_AGG сделать еще одну агрегацию.

      SELECT '[' + STRING_AGG(CAST(v.json AS nvarchar(max)), ',') + ']'
FROM T
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('ID', CAST(ID AS nvarchar(100))),
        ('Name', Name),
        ('Program', Program),
        ('Type', [Type]),
        ('Section', Section),
        ('Director', Director),
        ('Project', Project),
        ('Sr Manager', [Sr Manager]),
        ('PCM', PCM),
        ('Contractor', Contractor),
        ('Cost Client', [Cost Client])
    ) v([key], value)
    FOR JSON PATH
) v(json)

db<> скрипка

Вы не можете использовать FOR JSON снова, потому что тогда вы получите ["json": [{"key" : ...

Answer 2

прежде всего проверьте эту ссылку, вы можете найти то, что хотите

формат-запрос-результаты-как-json-с-для-json-sql-server

но в вашем случае вы можете попробовать это

       SELECT 
    ID,Name,Program,Type,Section,
    Director,Project,Sr,Manager,PCM,Contractor,Cost,Client
 FROM table  
 FOR JSON AUTO;

проверьте ссылку, есть еще образец, чтобы он мог вам помочь