Cast JSON array values in MySQL

Question

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.

What version of MySQL (i.e. what does SELECT VERSION(); return)? — Bill Karwin
– Bill Karwin, Commented Apr 29, 2022 at 22:47

Bill Karwin · Accepted Answer · 2022-04-30 06:43:02Z

4

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

answered Apr 30, 2022 at 6:43

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

1 Comment

BenMorel Over a year ago

Exactly the answer I was looking for. Thanks!

Collectives™ on Stack Overflow

Cast JSON array values in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related