0

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
David Kade
  • 33
  • 5

2 Answers2

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

Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • Wow. Genius :-) – David Kade Sep 14 '21 at 11:59
  • one slight problem I noticed when I ran your script for a large number of rows, the output gets trucated. truncated at 65536 characters. – David Kade Sep 14 '21 at 12:02
  • Tried the cast option, still, the same result (gets truncated). Also tried creating a table with nvarchar(max) column and inserted the select result into the table still had the same issue. – David Kade Sep 14 '21 at 23:46
  • Are you *sure* it's not just SSMS doing that, see https://stackoverflow.com/questions/48595442/sql-server-for-json-path-statement-does-not-return-more-than-2984-lines-of-j Try also `SELECT LEN(STRING_AGG(v.json))...` that will tell you how long it actually is – Charlieface Sep 14 '21 at 23:48
  • Len also giving the same number of characters, but I am sure there should be more characters. Also, I can see the ending of the string is ,{"key":"zip_code","va as you can see value got truncated. – David Kade Sep 15 '21 at 11:01
  • create table TestTable ( col1 nvarchar(max) ) insert into TestTable SELECT '[' + STRING_AGG(cast(v.json as nvarchar(max)), ',') + ']' ... I did this even then result is getting truncated . – David Kade Sep 15 '21 at 11:06
  • Again, if using SSMS, it will not display more than 65535 characters anyway. You can export it to a file using `sqlcmd`, or by using some client app such as C#, and you can check the real length using the method I show, it definitely works as you can see in this fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9e2616a885593ceb007361d2ee5d3eb5 – Charlieface Sep 15 '21 at 14:13
  • Yes, you are right :-) I got it working using SSIS. Thanks, Mate, much appreciated :-) – David Kade Sep 16 '21 at 01:52
0

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

Reza Faghani
  • 141
  • 1
  • 10