Mysql comma seperated to json-array

Question

I have some fields in the database that are comma selerated, something like: a,b,c,d,e

and I want to convert them into:

["a","b","c","d","e"]

I know how to do it in nodejs / any other language, but I need to do it directly on the mysql
Possible? Thanks

You fell victim to one of the classic blunders - the most famous of which is "never get involved in a land war in Asia" - but only slightly less well-known is this: "Never put csv values in a table column!" — Joel Coehoorn
– Joel Coehoorn, Commented Nov 3, 2020 at 14:48

GMB · Accepted Answer · 2020-11-03 14:35:13Z

2

The simplest approach probably is to use string functions only:

select concat('["', replace(col, ',', '","'), '"]') js from mytable

Basically this turns a string 'a,b,c,d,e' to '["a","b","c","d","e"]' - which MySQL will happily understand as JSON, if you use JSON functions on it.

Note that this only works as long as your CSV elements do not contain embedded double quotes.

answered Nov 3, 2020 at 14:35

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Akina Over a year ago

this only works as long as your CSV elements do not contain embedded double quotes In such case nested replace will help.

Collectives™ on Stack Overflow

Mysql comma seperated 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