November 27, 2020 at 9:34 am
hi i have one doubt in sql server.
how to convert json format using multiple tables.
emp_addr:
empid | city|state|country|doj
1 | bang|KA |Ind |2018-08-03
2 | chen|Tn |Ind |2018-02-04
emp_addressline:
empdid |addressline |addresscode
1 | street1 |street1
1 | street2 |taluka
1 | street2 |district
2 | street1 |street1
2 | street3 |district
emp_proof:
empdid |prooftype
1 |aadhar
2 |voterid
based on above data i want output like below .
empid |city |doj |empjson
1 |bang |2018-08-03 |{"city":"bang","state":"ka","country":"ind","addreslineinfo":{"street1":"stree1","taluka":"street2","district":street2"},"prooftype":"aadhar"}
2 |chen |2018-02-04 |{"city":"bang","state":"ka","country":"ind","addreslineinfo":{"street1":"stree1","district":street3"},"prooftype":"voterid"}
I tried like below :
select empid,city,doj,(select city,state,country, addresscode,addressline,prooftype for json path ,include_null_values,without_array_wrapper)empjson
from emp_addr a inner join emp_addressline b on a.empid=b.empid
inner join emp_proof c on a.empid=c.empid
above query not given expected result ,could you please tell me how to write a query to achive this task in sql server .
November 28, 2020 at 10:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 28, 2020 at 2:04 pm
The JSON is oddly formed. Are you 100% sure this is the format required? The inner JSON items are in array list format but there's no array wrapper. I don't see how could this even be parsed reliably. Also, there doesn't appear to be a way to reliably order the addresslines
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply