Reputation: 13
I am trying to create a JSON file from sql server 2016 (Tsql). The desired output should be something like below
[{
"name": "xxxx",
"data": [
["Col1Name", Col1Value],
["Col2Name", Col2Value],
["Col3Name", Col3Value],
["Col4Name", Col4Value],
["Col5Name", Col5Value],
]
}]
or it can be something like
{
"name": "xxxxxx",
"data": [Col1Value, Col2Value, Col3Value, Col4Value, Col5Value]
}
I tried few sql queries using JSON AUTO and JSON PATH as shown below but none of them gets me the desired output. Any help is highly appreciable.
SELECT f.name,
data.OneMonth,
data.ThreeMonth,
data.YTD,
data.OneYear,
data.ThreeYear,
data.FiveYear,
data.TenYear,
data.SinceIncept
FROM dbo.Fund f
JOIN dbo.FundPerf data on f.FundId = sc.FundId
FOR JSON AUTO
Upvotes: 1
Views: 554
Reputation: 29234
I don't think that's possible with a simple query. You can always use a stored procedure and JSON_MODIFY
:
create table #temp (name varchar(30), col1 varchar(30), col2 varchar(30))
insert into #temp values ('Jason', 'hello', 'world'), ('yeshwanth', 'goodbye', 'world')
create or alter procedure sp_get_json @json varchar(max) out as
begin
set @json = '[]'
declare @name varchar(30), @col1 varchar(30), @col2 varchar(30), @item varchar(max)
declare c cursor for select name, col1, col2 from #temp
open c
fetch c into @name, @col1, @col2
while @@FETCH_STATUS = 0
begin
set @item = JSON_MODIFY('{}', '$.name', @name)
set @item = JSON_MODIFY(@item, '$.data', JSON_QUERY('[]'))
set @item = JSON_MODIFY(@item, 'append $.data', @col1)
set @item = JSON_MODIFY(@item, 'append $.data', @col2)
set @json = JSON_MODIFY(@json, 'append $', JSON_QUERY(@item))
fetch c into @name, @col1, @col2
end
close c
deallocate c
end
declare @j varchar(max)
exec sp_get_json @json = @j out
print @j
Upvotes: 1
Reputation: 13403
You can try this.
DECLARE @Table TABLE (name varchar(10), Col1Name VARCHAR(10),Col2Name VARCHAR(10),Col3Name VARCHAR(10),Col4Name VARCHAR(10),Col5Name VARCHAR(10))
INSERT INTO @Table VALUES ('xxx', 'Col1Value','Col2Value','Col3Value','Col4Value','Col5Value')
SELECT name
, Col1Name as 'data.Col1Name'
, Col2Name as 'data.Col2Name'
, Col3Name as 'data.Col3Name'
, Col4Name as 'data.Col4Name'
, Col4Name as 'data.Col5Name'
FROM @Table
FOR JSON PATH
Result:
[
{
"name":"xxx",
"data":{
"Col1Name":"Col1Value",
"Col2Name":"Col2Value",
"Col3Name":"Col3Value",
"Col4Name":"Col4Value",
"Col5Name":"Col4Value"
}
}
]
Upvotes: 1