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"}
1 |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 .