英文:
Migrate comma separated string to json array
问题
我想将它们转换成JSON数组，以便我可以使用MySQL JSON数组类型及其关联的方法。
所以基本上我正在寻找一种方法，将technician,director,website designer转换为["technician","director","website designer"]在SQL中。
列表的长度是任意的。
我遇到的最大问题是如何在逗号分隔的字符串中的每个元素上应用SQL函数（例如，我可以运行JSON_QUOTE()在每个元素上），因为添加括号只是一个简单的CONCAT。
解决方案应适用于MySQL 5.7。
英文:
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.
答案1
得分: 9
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
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论