Yii2: Json database field: request for ability to save php null value (at the root) as SQL NULL ( now saves only as null::json)

[mgrechanik]

Yii db engine saves simple column types with null php value as SQL NULL (field 'x' in example), but for json type it saves them only as null::json, which is correct json of course, but would not it be nice to have control about saving it as SQL NULL if needed?

What steps will reproduce the problem?

PostgreSql 9.6

create table somedata 
(
        id integer not null primary key,
    x integer,
    y json
)

        $db = \Yii::$app->db;
        // 1) y -> sql NULL
        $db->createCommand('INSERT INTO {{somedata}} VALUES(1, null, null)')->execute();
        // 2)   !!!!!   
        $db->createCommand()->insert('{{somedata}}', [
            'id' => 2,
            'x' => null,
            'y' => null,
        ])->execute();        
        // 3) for ActiveRecord
        $obj = new Somedata();
        $obj->id = 3;
        $obj->x = 1;
        $obj->y = ['x' => 'y'];
        $obj->save();
        // 4)  !!!!!
        $obj2 = new Somedata();
        $obj2->id = 4;
        $obj2->x = null;
        $obj2->y = null;
        $obj2->save();

select id, x , y , json_typeof(y) as y_jsontype, y is NULL as y_isnull from somedata;

What do you get ?

 id | x |     y     | y_jsontype | y_isnull
----+---+-----------+------------+----------
  1 |   |           |            | t
  2 |   | null      | null       | f
  3 | 1 | {"x":"y"} | object     | f
  4 |   | null      | null       | f

As you see in cases 2) and 4) saves as null::json

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.14.1
| PHP version | 7
| Operating system | Windows

[SilverFire]

Thank you for the report. Would you like to try to submit a PR?

[SilverFire]

Fixed

[mgrechanik]

1) I did not really mean to simply drop possibility of null::json (*), but leave user with a choise.
It all started from this: http://yiiframework.ru/forum/viewtopic.php?f=27&t=46795

(*) maybe someone uses?, and there is a difference ...

2) Don't you think this goes to Mysql eighter?

[SilverFire]

Just thought that JsonExpression(null) should never be casted to NULL because it just doesn't make sense: in case I say it's a JSON expression, it should be JSON.

[SilverFire]

The problem is partially related to https://github.com/yiisoft/yii2/issues/15837 because requires a possibility to configure behavior for specific column.

[SilverFire]

Moving to 2.0.15. For now you can use new Expression(null) to prevent automatic typecasting to JSON null

[mgrechanik]

It should be new \yii\db\Expression('NULL')
I think this solves the problem the issue was created for.

[SilverFire]

In order to keep null-handling behavior consistent with other data types, NULL value in ActiveRecord will be always saved as SQL NULL for both JSON and Array columns.

[SilverFire]

In case you need to save value as JSON null – you must wrap it in JsonExpression explicitly