how to get jsonstring along columns in sql server

Question

hi i have one doubt in sql server .
how to get json format filed using few columns and get columns also.

table : emp
id | name | sal | depno
1 | a |100 |`10
2 | b |200 |20

based on above data i want output like below :

id |name | sal | deptno |empjsonstring
1 | a | 100 |10 |{"id":1,"name":"a","sal":100,"deptno":10}
2 | b | 200 |20 |{"id":2,"name":"b","sal"200,"deptno":20}

i tried like below :
select * from emp for json path,include_null_values,without_arry_wrapper

but above query not give expect result.

could you please tell me how to write a query to achive this task in sql server

Answer 1

This seems to work:

select *, (select id, [name], sal, depno for json path, include_null_values, without_array_wrapper) as empjsonstring
from emp

Answer 2

Hi @harinathu ，

I tested the Viorel-1 method and it worked.

    select *,(select id,name,sal,depno for json path, include_null_values, without_array_wrapper) empjsonstring  
    from emp  

Regards
Echo

---

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.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table