Share via

data in json format

dinesh 41 Reputation points
2022-10-26T17:33:05.9+00:00

I have employee table. I have below fields. Few employees multipe mobile and home numbers. I want data in json format sending empid in where condition.

Select empid, empname, address, homenumber from dbo.emp

values in homenumber = 1^2^3^4
address = Home1^Home2^Office1^Office2

I want above data in json format for all these fields - empid, empname, address, homenumber

"Address": [{"Homenumber": 1, "address ":"Home1"},{"Homenumber": 2, "address ":"Home2"},{"Homenumber": 3, "address ":"Home3"},
{"Homenumber": 4, "address ":"Home4"}]

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sudipta Chakraborty - MSFT 1,106 Reputation points Microsoft Employee
    2022-10-26T18:17:23.71+00:00

    @dinesh :
    Could you please check the below mentioned query helps to solve the problem, tweak it based on your needs. 

    SELECT   
     (  
     Select  address, homenumber from dbo.emp  
     FOR JSON PATH  
     ) Address  
, em.empid,em.empname from dbo.emp em  
        FOR JSON PATH
  2. Ronen Ariely 15,186 Reputation points
    2022-10-27T14:30:06.39+00:00

    Using SQL Server 2022 this is simple using the new functions 

    use tempdb  
GO  
  
drop table if exists T  
GO  
  
create table T (  
 Numbers VARCHAR(MAX),  
 Addresses VARCHAR(MAX)  
)  
GO  
  
INSERT T(Numbers,Addresses) values ('1^2^3^4','Home1^Home2^Office1^Office2')  
GO  
  
SELECT [address] = t2.[value], [Homenumber] = t1.[value]  
FROM T  
CROSS APPLY string_split(T.Numbers, '^', 1) t1  
CROSS APPLY string_split(T.Addresses, '^', 1) t2  
WHERE t1.ordinal = t2.ordinal  
FOR JSON AUTO  
GO

    Above query returns: [{"address":"Home1","Homenumber":"1"},{"address":"Home2","Homenumber":"2"},{"address":"Office1","Homenumber":"3"},{"address":"Office2","Homenumber":"4"}]

    Which if I format it to pretty JSON you get 

    [  
  {  
    "address": "Home1",  
    "Homenumber": "1"  
  },  
  {  
    "address": "Home2",  
    "Homenumber": "2"  
  },  
  {  
    "address": "Office1",  
    "Homenumber": "3"  
  },  
  {  
    "address": "Office2",  
    "Homenumber": "4"  
  }  
]

    I assume that the OP is using older version, so I will wait for the missing information we asked above in the comment :-)

    Note! Before version 2022, we cannot count on the order that the function string_split returns! Therefore it is not a solution

    0 comments