Convert result into doubly nested JSON format

Question

I am trying to convert SQL Server results into a doubly nested JSON format.

Source SQL Server table:

ID	Name	Program	Type	Section	Director	Project	Sr Manager	PCM	Contractor	Cost Client
123	abc	qew	tyu	dd	ghghjg	hkhjk	fghfgf	gnhghj	gghgh	gghhg
456	yui	gdffgf	ghgf	jkjlkll	uiop	rtyuui	rfv	ujmk	rfvtg	efgg

Convert into doubly JSON as shown here:

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

Any help would be greatly appreciated.

Edit: I started with this by rewriting the "FOR JSON AUTO" so that I can add "Key" "Value" text somehow. But because my table has space in the column name, FOR XML PATH('') giving invalid XML identifier as required by FOR XML error. that is when I thought of taking community help.

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

Sample data:

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

You can use the following code:

• Inside an APPLY, unpivot the columns as key/value pairs...
• ... and aggregate using FOR JSON PATH
• Use STRING_AGG to do another aggregation.

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<>fiddle

You cannot use FOR JSON again, because then you will get ["json": [{"key" : ...