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.

Demarco

Answer 1

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

Mussulman

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. – Demarco