get childstring json format

Question

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 .

Answer 1

Hi @ravibabu ，

Please refer updated query from below:

declare @sql nvarchar(max)  
 declare @s nvarchar(max)  
 declare @s1 nvarchar(max)  
      
 SELECT @s = STUFF((  
             SELECT distinct ',[' + trim(addresscode)+']'  
             FROM emp_addressline  
             FOR XML PATH('')  
             ), 1, 1, '')  
 FROM emp_addressline  
      
 SELECT @s1 = STUFF((  
             SELECT distinct ',trim([' + trim(addresscode)+']) ['+trim(addresscode)+']'  
             FROM emp_addressline  
             FOR XML PATH('')  
             ), 1, 1, '')  
 FROM emp_addressline  
      
 set @sql= N'  
 select a.empid, city, doj,  
      (  
          select   
              trim(city) city,   
              trim(state) state,   
              trim(country) country,  
              (  
                      select '+@s1+'  
                      from emp_addressline  
                      pivot  
                      (  
                          max(addressline) for addresscode in ('+@s+')  
                      )  
                      as p  
                      where empid = a.empid  
                   for json path  
              ) as addreslineinfo,  
              trim(p.prooftype) prooftype  
          for json path  
      ) as empjson  
  from emp_addr a  
  left join emp_proof p on p.empid = a.empid'  
      
 EXECUTE sp_executesql @sql  

Output:
empid city doj empjson
1 bang 2018-08-03 [{"city":"bang","state":"KA","country":"Ind","addreslineinfo":[{"district":"street2","street1":"street1","taluka":"street2"}],"prooftype":"aadhar"}]
2 chen 2018-02-04 [{"city":"chen","state":"Tn","country":"Ind","addreslineinfo":[{"3":"street4","district":"street3","street1":"street1"}],"prooftype":"voterid"}]

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @ravibabu ,

Thank you so much for posting here in Microsoft Q&A.

Based on Viorel's answer, you could refer below dynamic way if “street1”, “taluka” and “district” are always changing and removing the extra spaces.

declare @sql nvarchar(max)  
declare @s nvarchar(max)  
declare @s1 nvarchar(max)  
  
SELECT @s = STUFF((  
            SELECT distinct ',' + trim(addresscode)  
            FROM emp_addressline  
            FOR XML PATH('')  
            ), 1, 1, '')  
FROM emp_addressline  
  
SELECT @s1 = STUFF((  
            SELECT distinct ',trim(' + trim(addresscode)+') '+trim(addresscode)  
            FROM emp_addressline  
            FOR XML PATH('')  
            ), 1, 1, '')  
FROM emp_addressline  
  
set @sql= N'  
select a.empid, city, doj,  
     (  
         select   
             trim(city) city,   
             trim(state) state,   
             trim(country) country,  
             (  
                     select '+@s1+'  
                     from emp_addressline  
                     pivot  
                     (  
                         max(addressline) for addresscode in ('+@s+')  
                     )  
                     as p  
                     where empid = a.empid  
                  for json path, include_null_values  
             ) as addreslineinfo,  
             trim(p.prooftype) prooftype  
         for json path, include_null_values  
     ) as empjson  
 from emp_addr a  
 left join emp_proof p on p.empid = a.empid'  
  
EXECUTE sp_executesql @sql  

Output:

empid city doj empjson  
1 bang            2018-08-03 [{"city":"bang","state":"KA","country":"Ind","addreslineinfo":[{"district":"street2","street1":"street1","taluka":"street2"}],"prooftype":"aadhar"}]  
2 chen            2018-02-04 [{"city":"chen","state":"Tn","country":"Ind","addreslineinfo":[{"district":"street3","street1":"street1","taluka":null}],"prooftype":"voterid"}]  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Assuming that “street1”, “taluka” and “district” represent constant codes, try this approach too:

select a.empid, city, doj,
    (
        select 
            city, 
            state, 
            country,
            (
                    select street1, taluka, district
                    from emp_addressline
                    pivot
                    (
                        max(addressline) for addresscode in (street1, taluka, district)
                    )
                    as p
                    where empid = a.empid
                 for json path, include_null_values
            ) as addreslineinfo,
            p.prooftype
        for json path, include_null_values
    ) as empjson
from emp_addr a
left join emp_proof p on p.empid = a.empid

(You can use varchar type or rtrim function to remove spaces).