Convert data into JSON format-CodePudding

I have employee table. I have below fields. Few employees multiple 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 these fields are

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

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

I tried below query but getting wrong output

 select empid, r.value , r1.value from dbo.emp
      cross apply string_split (Homenumber,'^') r
         cross apply string_split (address ,'^') r1 where empid=1

I'm supposed to get data in below format

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

but since I have done cross join, it is getting more records. I would be great if someone can help me.

CodePudding user response：

Unfortunately, STRING_SPLIT does not at the moment offer an ordinality column, so you cannot join the two splits on that.

Instead you can hack it by creating JSON and then using OPENJSON.

Finally combine it all together using FOR JSON, all inside a subquery

SELECT
  e.empid,
  (
    SELECT
      Homenumber = r1.value,
      address = r2.value
    FROM OPENJSON('["'   REPLACE(STRING_ESCAPE(e.Homenumber, 'json'), '^', '","')   '"]') r1
    JOIN OPENJSON('["'   REPLACE(STRING_ESCAPE(e.address, 'json'), '^', '","')   '"]') r2 ON r2.[key] = r1.[key]
    ORDER BY r1.[key]
    FOR JSON PATH, ROOT('Address')
  )
FROM dbo.emp e
WHERE e.empid = 1;

CodePudding user response：

select [data] from [table] for **JSON Auto**

will convert results into JSON format