How to explode a string by comma with json object

Question

I have a problem, I have a varchar field that contains m values ​​separated by commas:

garage 1,2 
garage 1,3,5

I have the following query

select
           json_object(
                      'data',
                      json_objectagg(
                          r.slug,
                          json_object(
                              'nome',r.name,
                              'localizacao',
                              json_object(
                                  'endereco' , r.address,
                                  'bairro' , r.neighborhood,
                                  'cidade' , r.city,
                                  'estado' , r.state,
                                  'latitude' , r.lat,
                                  'longitude' , r.lng
                                ),
                              'tipo' , r.type,
                              'incorporadora' , d.name,
                              'dormitorios', json_object('legenda', r.rooms, 'quantidade', json_array(r.rooms_quantity)),
                              'garagem', json_object('legenda', r.garage, 'quantidade', json_array(r.garage_quantity)),
                              'metragem', json_object('legenda', r.footage, 'minimo', r.min_footage, 'maximo', r.max_footage),
                              'preco', json_object('minimo', IFNULL(r.min_value, 0), 'maximo' , IFNULL(r.max_value, 0)),
                              'capa' , ri.filename_default
                            )
                        )
            ) as jsonExport
        from realties r
               inner join developers d on r.developer_id = d.developer_id
               inner join realties_images ri on r.realty_id = ri.realty_id and ri.type_image = 'cover'
        where r.active = 'yes' and d.active = 'yes' and ri.active = 'yes';

But when returning json_array with integers it does not match quotes.

being this ["2,Studio"] ["1,2,3"]

but I needed it to be an array because that other code will fetch the information and I needed it

["2","Studio"] ["1","2","3"] 

I already tried array_objectagg and everything is something, can anyone help me?

Answer 1

1. The best solution would be to normalize the database and avoid storing multiple values in one field.

2. Another solution would be to retrieve the resultset and use some kind of server scripting, process it and then convert it to JSON.

3. You could write your own MySQL functions to explode comma separated values, e.g like this. However, my advise would be to stick with #1. It will save you time in a long term.