How to convert Doctrine type='array' to Doctrine type='json' in a MySQL database?

Question

I have a database that uses doctrine to generate columns. One columns is using the type='array'. I would like to convert it to a type='json'. How can I do this using MySQL query?

I tried the following MySQL command:

ALTER TABLE user_parameter ADD ma_variable_tmp JSON NOT NULL;*/
UPDATE user_parameter SET ma_variable_tmp = CAST(pharmaciedevdata.user_parameter.value AS JSON) WHERE id>=1;
ALTER TABLE user_parameter DROP ma_variable;
ALTER TABLE user_parameter RENAME COLUMN ma_variable_tmp TO value;

And I got this response: Error Code: 3141. Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0. 0.000 sec

Answer 1

Following @A.L. idea, I've end up we this code. The solutions uses, mostly PHP and can be done using Doctrine Migration.

<?php
    
    declare(strict_types=1);
    
    namespace DoctrineMigrations;
    
    use Doctrine\DBAL\Schema\Schema;
    use Doctrine\Migrations\AbstractMigration;
    
    /**
     * Auto-generated Migration: Please modify to your needs!
     */
    final class Version20230622200546 extends AbstractMigration
    {
        public function getDescription(): string
        {
            return '';
        }
    
        public function up(Schema $schema): void
        {
            $connection = $this->connection;
    
            // Fetch the existing rows from the table
            $rows = $connection->fetchAll('SELECT * FROM user_parameter');
    
            // Iterate over each row
            foreach ($rows as $row) {
                $id = $row['id'];
                $data = $row['value'];
    
//Unserialize data first since type array is serialize data
                $encodedData=unserialize($data);
    
    
                // Update the row with the new encoded data
                $encodedData = json_encode($encodedData); // Encode the data using json_encode
    
    
    
                $connection->executeUpdate('UPDATE user_parameter SET value = :encodedData WHERE id = :id', [
                    'encodedData' => $encodedData,
                    'id' => $id,
                ]);
            }
        }
    
        public function down(Schema $schema): void
        {}
    }