Yii2 & MySQL JSON type: Saved double encoded in db

Yii 2.0 General Discussions
1

Hi.

I use Yii 2.0.38 and always worked with Json inside a MySQL LONGTEXT column. Since MySQL supports the more efficient json column type, I wrote a migration like:

$this->alterColumn( 'log', 'data', $this->json() );

which worked. MySQL is reporting this column to be a json column. All data was converted, no errors.

Until now, I saved data like:

        $log->data = \yii\helpers\Json::encode($data);
        if(!$log->validate()) {
            \Yii::error($log->getErrors());
        }
        $log->save();

this usage ends now in double quoted data like:

"{\"id\":1,\"type\":\"update\",\"changed\":{\"noMails\":[\"0\"],\"updated_at\":{\"1605612803\":1605612914}}}"

which was previously stored as

{"id": 1, "type": "update", "changed": {"updated_at": {"1605254212": 1605596615}, "last_login_time": {"1605254212": 1605596615}}}

So, I removed the encode. Which results in the same. I tried to added JsonExpression() but the result is still the same.

How can I set the attribute value to json and store it as native json 1:1 without double encoding? Am I doing something wrong?

$data is a php array with the data to be stored, not a string.

Thanks in advance!

3

Since you seem just to registered for this one post on this forum I doubt that this answer was meant to be serious :wink:

If Im wrong, please post your ideas in public here.

1 Like
4

When MySQL has json as type for the coumn and assign via $model->data = $dataarray Yii produces:

INSERT INTO `log` (`user_id`, `model`, `data`, `created_at`) VALUES (1, 'common\\models\\MasterUser', CAST('\"{\\\"id\\\":1,\\\"type\\\":\\\"update\\\",\\\"changed\\\":{\\\"noMails\\\":[\\\"0\\\"],\\\"updated_at\\\":{\\\"1605615533\\\":1605615782}}}\"' AS JSON), 1605615782)

Where does the double encoding comes from?

5

Might be a problem with PDO and the driver? I’m not for sure.
Could you post the versions of your php and the driver?

6

PHP: 7.4.12
PDO_Mysql: mysqlnd 7.4.12
MySQL-Server 5.7.32

I also referenced to this issue which seems the same (at least for storing data INTO Mysql): https://github.com/yiisoft/yii2/issues/17712

7

Thanks. I confirmed that they are the latest. So PDO should not be the cause.

I agree with you. It looks like the same issue.

8

Solved!

That was weird: I created another field for another model with json type as well and here it was working out-of-the-box. After digging deeper, I found an overwritten afterSave function iunside the problematic model which encoded the array still as json…

So, no problem here anymore, everything is working as expected!

1 Like