Including 'bit' values from SQL to JSON, Sql Server

Including 'bit' values from SQL to JSON

--Table 1 to store all answers
create Table ans(qid int, ans varchar(50), aid varchar(10))
insert into ans values
       (1001,'test answer',null),
      (1002,null,a),
      (1003,null,1),
      (1004,null,0)
      
--Table 2 to pivot answers
create table #temp(cid int,[1001] varchar(50),[1002] varchar(10), [1003] bit, [1004] bit)
insert into #temp
select 12345,
        max(case when qid = '1001' then a.ans) as [1001],
         max(case when qid = '1002' then a.aid) as [1002],
          max(case when qid = '1003' then a.aid) as [1003],
           max(case when qid = '1004' then a.aid) as [1004]
from #ans a

--Doing an XML final select
create table #final (xml_data varchar(max))
insert into #final
select 
final_data = 
      (select
        fakedata = 'something..',
        question1 = t.[1001],
        question2 = t.[1001],
        question3 = t.[1003],
        question4 = t.[1004]
        from #temp
      FOR XML PATH(''), TYPE )

select * from  #ans
select * from  #temp
select * from  #final
 
I take this XML output and convert into JSON in Online. The problem is:

wrong JSON structure:
{
   "fakedata": "something..",
   "question1": "test answer",
   "question2": "a"
   "question3": "1"
   "question4": "0"
   }
}

In this format, I want question 3&4 to be bit like

desired JSON structure:
{
   "fakedata": "something..",
   "question1": "test answer",
   "question2": "a"
   "question3": 1
   "question4": 0
   }
}

I am unable to convert anywhere.

drop table #ans,#temp,#final


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development