How can I convert a string to a json string with SQL Server

Question

Example: now my string is

aa, bb, cc

How can I convert it to

[{"name","aa"},{"name":"bb"},{"name":"cc"}]

with SQL Server

Either split the string and use for json or just manually alter the string with replace. — ZLK
– ZLK, Commented Nov 29, 2018 at 3:13

Avi · Accepted Answer · 2018-11-29 03:14:36Z

2

One way to achieve this is using String split and JSON path function.

create table jsonprac
(name varchar(100) )

insert into jsonprac values ('aa,bb,cc') 

select   
(select s.value as [name]   from  jsonprac p 
 cross apply string_split(p.name,',') s
 for json path, INCLUDE_NULL_VALUES ) JsonValue

Output:

JsonValue
[{"name":"aa"},{"name":"bb"},{"name":"cc"}]

answered Nov 29, 2018 at 3:14

Avi

1,8453 gold badges18 silver badges32 bronze badges

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

1 Comment

marc_s Over a year ago

Of course, this only works in SQL Server 2016 or newer which has the for json path clause

Collectives™ on Stack Overflow

How can I convert a string to a json string with SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related