Get childstring json format

Transact-SQL
, ,
1

hi i have one doubt in sql server.
how to convert json format using multiple tables
create table emp_addr(empid int,city char(15),state char(15),country char(15),doj date)
insert into emp_addr values(1,'bang','KA','Ind','2018-08-03'),(2,'chen','Tn','Ind','2018-02-04')
create table emp_addressline(empid int,addressline char(15),addresscode char(15))
insert into emp_addressline values(1,'street1','street1'),(1,'street2','taluka'),(1,'street2','district')
,(2,'street1','street1'),(2,'street3','district')
create table emp_proof(empid int,prooftype char(15))
insert into emp_proof values(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 a.empid,city,doj,
Concat((select city,state,country for JSON path),
(select addresscode,addressline,prooftype for json path, Root('addreslineinfo') ,include_null_values)
)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 .

2

when using json path, it uses the column name as the key and the column data as value to create your json key/value

what you want is a concatenation of the column data values. so json path is not going to work for that.

Also the following is not a valid json

select ISJSON('{"city":"bang","state":"ka","country":"ind",
"addreslineinfo":{"street1":"stree1","taluka":"street2","district":street2"},"prooftype":"aadhar"}')
3

this should it but with a caveat. I am using literal data values. if in the future you have more addresses and more emp_proof data you will have to go with dynamic query.

;with src
as
(
	SELECT empid, [street1], [taluka], [district]
	FROM  
	(SELECT empid, trim(addresscode) as addresscode, 
trim(addressline) as addressline
		FROM #emp_addressline
	) AS SourceTable  
	PIVOT  
	(  
	max(addressline)  
	FOR addresscode IN ([street1], [taluka], [district])  
	) AS PivotTable
)
select a.empid,city,doj,
Concat(
        (select trim(city) as city,
		        trim(state) as state,
				trim(country) country, 
		        src.district as 'addreslineinfo.district', 
				src.street1 as 'addreslineinfo.street1', 
				src.taluka as 'addreslineinfo.taluka',
				trim(prooftype) as prooftype
				for JSON path
		),
		('')
      ) empjson
from #emp_addr a
join src on a.empid = src.empid
inner join #emp_proof c on a.empid=c.empid

image
image1222×97 4.4 KB

1 Like