Customize "for json auto" to get customized result-CodePudding

I am having below codes in SQL Server

declare @output table ([key] nvarchar(max) , val nvarchar(max))
insert into @output values ('key1' , 'val1' )
insert into @output values ('key2' , 'val2' )
select * from @output for json auto

which returns below result:

[{"key":"key1","val":"val1"},{"key":"key2","val":"val2"}]

I am wondering if there is any way to change my json result to something like nelow:

{"Key1": "val1" , "key2": "val2"}

CodePudding user response：

You cannot do this using FOR JSON unless you use dynamic SQL.

However, it's much easier to just use STRING_AGG. You need to make sure to properly escape using STRING_ESCAPE

SELECT '{'   STRING_AGG('"'   STRING_ESCAPE(o.[key], 'json')   '":"'   STRING_ESCAPE(o.val, 'json')   '"', ',')   '}'
FROM @output o;

Note that if you wanted a JSON array aggregated by a third column, say Id, you can use STRING_AGG a second time

SELECT '['   STRING_AGG(o.obj, ',')   ']'
FROM
    SELECT obj = '{"id":"'   STRING_ESCAPE(o.id, 'json')   '",'   STRING_AGG('"'   STRING_ESCAPE(o.[key], 'json')   '":"'   STRING_ESCAPE(o.val, 'json')   '"', ',')   '}'
    FROM @output o
    GROUP BY Id
) o;

CodePudding user response：

I found the solution:

select '{' string_agg('"' [key] '" : "' val '"' ,  ',') '}' from @output