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.
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"] |
+--------------------------------------------------------+