How to convert MySQL JSON array to comma separated string

Question

I have the following phone numbers in a column:

["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]

How can I get that info like this:

+63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403

Simple nested REPLACE() functions should do the trick, but the most clean version is to convert the JSON array to records and use GROUP_CONCAT().. So which MySQL version are you using? — Raymond Nijland
– Raymond Nijland, Commented Jul 1, 2019 at 12:10
You can use JSON_PRETTY (dev.mysql.com/doc/refman/5.7/en/…) to format the column — Nadir Latif
– Nadir Latif, Commented Jul 1, 2019 at 12:13
I though there might be a a function like implode: $array = array('lastname', 'email', 'phone'); $comma_separated = implode(",", $array); — Mihai Vinaga
– Mihai Vinaga, Commented Jul 1, 2019 at 12:14
Well you can use the REPLACE function to remove the following characters: '", [, ]' — Nadir Latif
– Nadir Latif, Commented Jul 1, 2019 at 12:17

Raymond Nijland · Accepted Answer · 2019-07-02 13:47:16Z

10

i think this is the most only MySQL clean way, atleast for MySQL versions under 8

Query

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT
  GROUP_CONCAT(
     JSON_UNQUOTE(
       JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
     )
  )                    

FROM (

  SELECT 
   @row := @row + 1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2
  CROSS JOIN (
    SELECT @row := -1 
  ) init_user_params 
) AS number_generator
CROSS JOIN (


SELECT 
    json
  , JSON_LENGTH(records.json) AS json_array_length    
FROM (

  SELECT 
   '["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]' AS json
  FROM 
   DUAL  
) AS records

) AS records 
WHERE
    number BETWEEN 0 AND  json_array_length - 1

Result

| GROUP_CONCAT(
     JSON_UNQUOTE(
       JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
     )
  ) |
| -------------------------------------------------------------------------------------------------------------------------- |
| +63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403                                                                |

see demo

Have you heard of JSON_TABLE()? – oysteing

I have, i dont assume everybody to be on MySQL 8 already but i added it for completeness also.

MySQL 8.0 query only

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
 GROUP_CONCAT(item)
FROM JSON_TABLE(
     '["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]'
   , "$[*]"

   COLUMNS (
       rowid FOR ORDINALITY
     , item VARCHAR(100) PATH "$"   
   )
) AS json_parsed

Result

| GROUP_CONCAT(item)                                          |
| ----------------------------------------------------------- |
| +63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403 |

see demo

The REPLACE() nesting method is more messy, but should work on all MySQL versions.

SELECT 
 REPLACE(
   REPLACE(
      REPLACE(
       '["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]'
       , '['
       , ''
     )
     , ']'
     , ''
   )
   , '"'
   , ''
 )

Result

| REPLACE(
   REPLACE(
      REPLACE(
       '["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]'
       , '['
       , ''
     )
     , ']'
     , ''
   )
   , '"'
   , ''
 ) |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| +63(02)3647766, +63(02)5467329, +63(02)8555522, +63(02)3642403                                                                                                                                      |

see demo

edited Jul 2, 2019 at 13:47

answered Jul 1, 2019 at 12:16

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

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

3 Comments

Mihai Vinaga Over a year ago

wow, my hat to you @Raymond if you wrote that query in the time I posted the question and when you posted the answer

Øystein Grøvlen Over a year ago

Have you heard of JSON_TABLE()?

Raymond Nijland Over a year ago

"Have you heard of JSON_TABLE()?" Thanks for the comment @oysteing yes i have but i dont assume everybody to be on MySQL 8 already but i added it for completeness also.

Talha · Accepted Answer · 2020-09-11 17:40:21Z

7

so if you have replace replace replace function. Its super cool and easy. if you want to build varchar values comma separated then use the following;

@json_array = ["value1", "value2"]

select replace(replace(replace(json_extract(@json_array, '$'), '"', '\''), '[', ''), ']', '');

But if you want to build numeric then use

select replace(replace(replace(json_extract(@json_array, '$'), '"', ''), '[', ''), ']', '');enter code here

answered Sep 11, 2020 at 17:40

Talha

1,64620 silver badges17 bronze badges

Comments

mili · Accepted Answer · 2023-06-23 05:34:15Z

Raymond already answered the question, but I am posting my answer to just make it clear when you get the JSON value directly from a table column. Let's say you have a person table and person table has phoneNoJson column in json format

select 
    p.phoneNosJson,
    ( 
        select 
           group_concat(phoneNo.value) 
        from 
           json_table(p.phoneNosJson, '$[*]' columns (value text path '$')) phoneNo
    ) phoneNosCommaSeparated
from 
person p

/* 
Result

if 
  p.phoneNosJson == ["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]
phoneNosCommaSeparated will be
  +63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403
*/

Hope my answer help to anybody having the same problem!!!

Fatalist · Accepted Answer · 2021-08-17 11:49:20Z

1

The most suitable solution for all MySQL versions is:

SELECT
    REPLACE(
        REPLACE(
            REPLACE(
                records.json,
                '[',
                ''
            ),
            ']',
            ''
        ),
        '"',
        ''
    ) AS comma_separated
FROM (
    SELECT
        '["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]' AS json
) AS records;

Looks heavy, but it works like a charm.

answered Aug 17, 2021 at 11:49

Fatalist

3593 silver badges5 bronze badges

Comments

Uzair Khan · Accepted Answer · 2019-07-01 12:26:41Z

-3

if it is not the query you want to do here is a simple function you can pass your array and get back the the string.

$data  = ["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"];
function MakeAstring($data){
     $array_with_comma = array();
    $last_key = end(array_keys($data));
  foreach($data as $key =>  $number)
   {
     $string .= $number.($key != $last_key ? ',': '');
   }
  return $string;
}
echo MakeAstring($data);

answered Jul 1, 2019 at 12:26

Uzair Khan

703 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to convert MySQL JSON array to comma separated string

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related