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,582 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Sudipta Chakraborty - MSFT 1,101 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,091 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 No comments