Stringify a JSON array in MySQL

Question

I have a JSON column in a table. i.e table

    column
---------------
[2,5,7,21,1,54,12]

Now its returning array for the below query.

select column from table

Output => [2,5,7,21,1,54,12]

What I want is Output as "2,5,7,21,1,54,12".

Any suggestion?

Answer 1

Here's a sample of querying a JSON array:

select data from t;
+--------------------------+
| data                     |
+--------------------------+
| [2, 5, 7, 21, 1, 54, 12] |
+--------------------------+

You can turn a JSON array into a string using JSON_UNQUOTE(). But it formats the string with square brackets and spaces:

select json_unquote(data) as stringified from t;
+--------------------------+
| stringified              |
+--------------------------+
| [2, 5, 7, 21, 1, 54, 12] |
+--------------------------+

You can remove those unwanted characters with REPLACE():

select replace(replace(replace(json_unquote(data), ' ', ''), '[', ''), ']', '') as stringified from t;
+------------------+
| stringified      |
+------------------+
| 2,5,7,21,1,54,12 |
+------------------+

In MySQL 8.0, you can replace the characters in one call to REGEXP_REPLACE():

select regexp_replace(json_unquote(data), '[\\[\\] ]', '') as stringified from t;
+------------------+
| stringified      |
+------------------+
| 2,5,7,21,1,54,12 |
+------------------+