CODEWITHSUNDEEP
sqljsont-sqlsql-server-2016
yeshwanth thummala
yeshwanth thummala

Reputation: 13

How to get JSON output from sql server 2016

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

Answers (2)

Jason Goemaat
Jason Goemaat

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

Serkan Arslan
Serkan Arslan

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

Related Questions