How to get json string along columns in SQL Server

Question

I have a question about SQL Server: how to get json format using a few columns and get columns also?

Table : emp

id  | name | sal | depno
----+------+-----+------
1   | a    | 100 | 10
2   | b    | 200 | 20

Based on this data, I want output like this :

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 with this query:

select * 
from emp 
for json path, include_null_values, without_array_wrapper

but that did not return the expect result.

Could you please tell me how to write a query to achieve this task in SQL Server?

SQL server 2012 doesn't support FOR JSON. You'll need to upgrade your instance to a version of SQL Server that does (SQL Server 2016+). — Thom A
– Thom A ♦, Commented Nov 20, 2020 at 9:37
Does this answer your question? How to make JSON from SQL query in MS SQL 2014 (Note that though this states 2014, the answer will work as the majority of 2014's features were Azure related and the new cardinality estimator. There weren't new functions like in 2016 and onwards) — Thom A
– Thom A ♦, Commented Nov 20, 2020 at 9:40

Zhorov · Accepted Answer · 2022-08-09 05:37:26Z

5

If you are using SQL Server 2016+ (with built-in JSON support) you simply need to build the JSON content using FOR JSON PATH for each row:

SELECT 
   id, name, sal, deptno,
   (SELECT id, name, sal, deptno FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS empjsonstring
FROM (VALUES
   (1, 'a', 100, 10),
   (2, 'b', 200, 20)
) emp (id, name, sal, deptno)

Result:

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}

edited Aug 9, 2022 at 5:37

answered Nov 20, 2020 at 9:54

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get json string along columns in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related