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

Source SQL Server table:

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

CodePudding user response：

first of all check this link you can find what you want

format-query-results-as-json-with-for-json-sql-server

but in your case you can try this

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

check the link there is more sample so it can help you

CodePudding user response：

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" : ...