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?

Normalize your database. What's the point storing such serialized datas ? — Cid
– Cid, Commented Apr 27, 2019 at 12:32
You can use nested replace() functions to remove the [ and ] or you can use a comination of SUBSTRING() and LENGTH(), see demo , there should be more MySQL string functions which can do the same. — Raymond Nijland
– Raymond Nijland, Commented Apr 27, 2019 at 13:34
@Cid this is for reporting purpose. I need to get it as stringify format. Whereas when the data gets inserted It needs to be in JSON array format. — surajit
– surajit, Commented Apr 27, 2019 at 14:30
@RaymondNijland : Yes thats the last option I can think.. There is no JSON function to join the array elements? — surajit
– surajit, Commented Apr 27, 2019 at 14:31
"I need to get it as stringify format. Whereas when the data gets inserted It needs to be in JSON array format" JSON array format is always with [] and also it sounds very contradictory with "What I want is Output as "2,5,7,21,1,54,12"." — Raymond Nijland
– Raymond Nijland, Commented Apr 27, 2019 at 14:33

Bill Karwin · Accepted Answer · 2019-04-27 17:07:34Z

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 |
+------------------+

JCH77 · Accepted Answer · 2021-05-07 13:13:01Z

An elegant solution is to use JSON_TABLE() and MySQL GROUP_CONCAT() capabilities.

With such a data sample :

+--------+--------------------------------------------------------------------+
| user   | emails (JSON)                                                      |
+--------+--------------------------------------------------------------------+
| user-1 | ["[email protected]", "[email protected]", "[email protected]"] |
| user-2 | ["[email protected]"]                                             |
| user-3 | ["[email protected]", "[email protected]"]                       |
+--------+--------------------------------------------------------------------+

If we want to output :

+--------+----------------------------------------------------------------+
| user   | emails (TEXT)                                                  |
+--------+----------------------------------------------------------------+
| user-1 | [email protected] // [email protected] // [email protected] |
| user-2 | [email protected]                                             |
| user-3 | [email protected] // [email protected]"                      |
+--------+----------------------------------------------------------------+

We can do :

WITH data_sample (user, emails) AS (
  -- Fake data build to test query
  VALUES 
    ROW ('user-1', JSON_ARRAY('[email protected]', '[email protected]', '[email protected]')),
    ROW ('user-2', JSON_ARRAY('[email protected]')),
    ROW ('user-3', JSON_ARRAY('[email protected]', '[email protected]'))
)
SELECT ALL user, GROUP_CONCAT(email SEPARATOR ' // ') AS emails
FROM data_sample
CROSS JOIN JSON_TABLE(emails, '$[*]' COLUMNS (email TINYTEXT PATH '$')) AS _
GROUP BY user;

Collectives™ on Stack Overflow

Stringify a JSON array in MySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related