CODEWITHSUNDEEP
mysql
BenMorel
BenMorel

Reputation: 36484

Cast JSON array values in MySQL

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.

Upvotes: 2

Views: 1496

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562250

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

Upvotes: 3

Related Questions