Migrate comma separated string to json array

Question

I have an old database, where some columns have comma separated strings stored like this technician,director,website designer

I want to convert them to a JSON array, so I can use the MySQL JSON array type and the methods associated with it.
So basically I am looking for a method to convert technician,director,website designer to ["technician","director","website designer"] in SQL.

The length of the list is arbitrary. The biggest struggle I am having is how to apply a SQL function on each element in the comma separated string, (So I for example can run the JSON_QUOTE() on each element) as adding the brackets are just a simple CONCAT.

The solution should be for MySQL 5.7.

Sebastian Brosch · Accepted Answer · 2020-01-03 10:29:55Z

10

You can use REPLACE to get the expected string:

SELECT CONCAT('["', REPLACE('technician,director,website designer', ',', '","'), '"]')
-- ["technician","director","website designer"]

Using JSON_VALID you can check if the result of the conversion is a valid JSON value:

SELECT JSON_VALID(CONCAT('["', REPLACE('technician,director,website designer', ',', '","'), '"]'))
-- 1

demo on dbfiddle.uk

edited Jan 3, 2020 at 10:29

answered Jan 3, 2020 at 10:21

Sebastian Brosch

43.8k15 gold badges78 silver badges89 bronze badges

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

1 Comment

Oliver Nybroe Over a year ago

Nice! Simple solution, did not think of that. I had one entry which was invalid, because it had a ", but, I just added a replace to escape it.

Collectives™ on Stack Overflow

Migrate comma separated string to json array

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