How can I use FOR JSON to build JSON in this format?

Question

I'd like to use FOR JSON to build a data payload for an HTTP Post call. My Source table can be recreated with this snippet:

drop table if exists #jsonData;
drop table if exists #jsonColumns;

select
    'carat' [column] 
into #jsonColumns
union
select 'cut' union
select 'color' union
select 'clarity' union
select 'depth' union
select 'table' union
select 'x' union
select 'y' union
select 'z'

select
    0.23 carat
    ,'Ideal' cut
    ,'E' color
    ,'SI2' clarity
    ,61.5 depth
    ,55.0 [table]
    ,3.95 x
    ,3.98 y
    ,2.43 z
into #jsonData
union
select 0.21,'Premium','E','SI1',59.8,61.0,3.89,3.84,2.31 union
select 0.29,'Premium','I','VS2',62.4,58.0,4.2,4.23,2.63 union
select 0.31,'Good','J','SI2',63.3,58.0,4.34,4.35,2.75
;

The data needs to be formatted as follows:

{
    "columns":["carat","cut","color","clarity","depth","table","x","y","z"],
    "data":[
        [0.23,"Ideal","E","SI2",61.5,55.0,3.95,3.98,2.43],
        [0.21,"Premium","E","SI1",59.8,61.0,3.89,3.84,2.31],
        [0.23,"Good","E","VS1",56.9,65.0,4.05,4.07,2.31],
        [0.29,"Premium","I","VS2",62.4,58.0,4.2,4.23,2.63],
        [0.31,"Good","J","SI2",63.3,58.0,4.34,4.35,2.75]
    ]
}

My attempts thus far is as follows:

select
    (select * from #jsonColumns for json path) as [columns],
    (select * from #jsonData for json path) as [data]
for json path, without_array_wrapper

However this returns arrays of objects rather than values, like so:

{
    "columns":[
        {"column":"carat"},
        {"column":"clarity"},
        {"column":"color"},
        {"column":"cut"},
        {"column":"depth"},
        {"column":"table"},
        {"column":"x"},
        {"column":"y"},
        {"column":"z"}
    ]...
}

How can I limit the arrays to only showing the values?

Answer 1

Honestly, this seems like it's going to be easier with string aggregation rather than using the JSON functionality.

Because you're using using SQL Server 2016, you don't have access to STRING_AGG or CONCAT_WS, so the code is a lot longer. You have to make use of FOR XML PATH and STUFF instead and insert all the separators manually (why there's so many ',' in the CONCAT expression). This results in the below:

DECLARE @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SELECT N'{' + @CRLF +
       N'    "columns":[' + STUFF((SELECT ',' + QUOTENAME(c.[name],'"')
                                   FROM tempdb.sys.columns c
                                        JOIN tempdb.sys.tables t ON c.object_id = t.object_id
                                   WHERE t.[name] LIKE N'#jsonData%' --Like isn't needed if not a temporary table. Use the literal name.
                                   ORDER BY c.column_id ASC
                                   FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,N'') + N'],' + @CRLF +
       N'    "data":[' + @CRLF +
       STUFF((SELECT N',' + @CRLF +
                     N'       ' + CONCAT('[',JD.carat,',',QUOTENAME(JD.cut,'"'),',',QUOTENAME(JD.color,'"'),',',QUOTENAME(JD.clarity,'"'),',',JD.depth,',',JD.[table],',',JD.x,',',JD.y,',',JD.z,']')
              FROM #jsonData JD
              ORDER BY JD.carat ASC
              FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,3,N'') + @CRLF +
      N'    ]' + @CRLF +
      N'}';

DB<>Fiddle