Yii2, Corrupted JSON object saved in MySQL JSON field

Question

In yii2 I have an associative array, I try to save it in JSON type MySQL table's field. However, the saved value is corrupted JSON object with \ back slashes.

The above screenshot from phpmyadmin. I saving the data by the code below:


// In the update action:
//...
if ($flag) {
                        $transaction->commit();
                        Yii::$app->getSession()->setFlash('success', 'Record has been updated!');
                        $this->savedi($model);
                        return $this->redirect(['view', 'id' => $model->id]);
                    }
//...

private function savedi($model)
{
    $output = [];
    foreach ($model->invoiceItems as $i => $item){
        $output['items'][$i]['id'] = $item->item_id;
        $output['items'][$i]['title'] = $item->item->title;
        $output['items'][$i]['qty']   = $item->qty;
    }
    $model->saved = json_encode($output);
    $model->save();
}

I don't know why the JSON is corrupted like that?

Have a look into: https://stackoverflow.com/a/22648879/7889695 — Sebastian Thadewald, Jun 20 '22 at 11:35
I have back slashes. and I tested it and the object's string in phpmysdmin still the same. — SaidbakR, Jun 20 '22 at 11:43
Now I see, that is complete normal, because special characters are escaped inside a MYSQL string. If you load back your model and var_dump/echo/xdebug it, you will see the backslashes are gone. Ref: https://github.com/yiisoft/yii2/blob/4c1cae3d6ddcf994478c0eb48df2bb63b338434a/framework/db/Query.php https://github.com/yiisoft/yii2/blob/4c1cae3d6ddcf994478c0eb48df2bb63b338434a/framework/db/Command.php#L468 — Sebastian Thadewald, Jun 20 '22 at 12:22
It looks like double encode to me. Yii2 has some support for JSON fields and it's doing json_encode by itself. Try `$model->saved = $output` instead of encoding it yourself. — Michal Hynčica, Jun 20 '22 at 12:24
@MichalHynčica Oh Yes! that works fine. Thank you. Please add this hint as answer. — SaidbakR, Jun 20 '22 at 12:27

score 1 · Accepted Answer · answered Jun 20 '22 at 12:33

The string in question looks like double encoded JSON.

The yii2 has some basic support for JSON fields which take care of encoding/decoding. So instead of calling json_encode() yourself you just need to assign array/object that should be encoded as it is.

private function savedi($model)
{
    $output = [];
    foreach ($model->invoiceItems as $i => $item){
        $output['items'][$i]['id'] = $item->item_id;
        $output['items'][$i]['title'] = $item->item->title;
        $output['items'][$i]['qty']   = $item->qty;
    }
    $model->saved = $output;
    $model->save();
}

Yii2, Corrupted JSON object saved in MySQL JSON field

1 Answers1