Cast JSON array values in MySQL-CodePudding

I have a MySQL JSON column containing values such as:

[33173,33386,24272,33499,33526,33347]

Would it be possible, with JSON functions, to cast each value in the array to string? The output would be:

["33173","33386","24272","33499","33526","33347"]

I'd like to avoid resorting to dirty REPLACE() calls.

CodePudding user response：

If you use MySQL 8.0, you can use the JSON_TABLE() function to explode the array into rows, then cast them to CHAR(), then JSON_ARRAYAGG() to implode the rows back into a JSON array.

set @j = '[33173,33386,24272,33499,33526,33347]';

select json_arrayagg(cast(i as char(5))) as j 
from json_table(@j, '$[*]' columns (i int path '$')) j;

Output:

 -------------------------------------------------------- 
| j                                                      |
 -------------------------------------------------------- 
| ["33173", "33386", "24272", "33499", "33526", "33347"] |
 --------------------------------------------------------

CodePudding user response：

The function replace() is likely going to be the cleanest and fastest method.

select 
  replace(
   replace(
     replace('[33173,33386,24272,33499,33526,33347]',
             '[','["'),
           ',','","'),
         ']','"]') as quoted_string
;

| quoted_string                                     |
| :------------------------------------------------ |
| ["33173","33386","24272","33499","33526","33347"] |

db<>fiddle here