SQL Server - convert a column of comma-separated string to JSON array into a new column

Question

I have a SQL Server database with a column called Categories containing comma-separated values in the format nvarchar(50). The length of each column is variable from record to record.

I've created a new empty column called CategoriesJSONArray. For all existing records, I need to convert the values in Categories to JSON arrays and write those converted values to CategoriesJSONArray.

How would I accomplish this using T-SQL, or from within the SSMS UI?

EDIT: example, as requested

Current:

Record   Categories

1        Sales, Support, Growth
2        Sales, Growth
3        Sales
4        Support, Growth, Sustain

Desired:

Record   Categories                  CategoriesJSONArray

1        Sales, Support, Growth      { "Sales" : "Support" : "Growth"}
2        Sales, Growth               { "Sales" : "Growth"}
3        Sales                       { "Sales" }
4        Support, Growth, Sustain    { "Sales" : "Growth" : "Sustain"}

Why don't you use a stored procedure to split the column based on the , and return it as a table. — SS_DBA
– SS_DBA, Commented May 12, 2017 at 15:25
The root of the issue is that you are storing delimited values in a single tuple. This violates 1NF and causes untold amounts of anguish. You will have to split those values first into a table. Here is an article with a number of excellent solutions to this problem. sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange
– Sean Lange, Commented May 12, 2017 at 15:33

JNevill · Accepted Answer · 2017-05-12 16:16:53Z

4

There's some ways to split a delimited string into records and then aggregate those back into JSON, but I think some concatenation and replacing would be a easier here:

SELECT record,
    Categories,
    '{"' + REPLACE(Categories, ', ', '":"') + '"}' as CategoriesJSONArray
FROM table

That may need some tweaking to deal with spaces and whatnot and it obviously won't work if your JSON becomes more complex, but it's quick and dirty.

edited May 12, 2017 at 16:16

answered May 12, 2017 at 16:12

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

4 Comments

John Cappelletti Over a year ago

Beat me by seconds

JNevill Over a year ago

I saw that, but yours was prettier! I'm glad that I am in good company with the solution though, I thought for sure it was too quick and dirty for SO with all the fancy strtok procedures and json-erizing that could be done.

JNevill Over a year ago

I just added a space after the comma in the string literal. I think that ought to get rid of those.

Stpete111 Over a year ago

@JNevill definitely makes sense, thanks! I'll test it out now and assuming all goes well I'll mark yours as the answer.

Collectives™ on Stack Overflow

SQL Server - convert a column of comma-separated string to JSON array into a new column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related