CODEWITHSUNDEEP
mysqlsqljson
surajit
surajit

Reputation: 63

Stringify a JSON array in MySQL

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?

Upvotes: 4

Views: 17787

Answers (2)

JCH77
JCH77

Reputation: 1363

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;

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562250

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

Upvotes: 7

Related Questions