MySQLのJSON型の特定要素の更新

JSON型の特定要素のみの更新についてのメモ

■準備

jsonを持つテーブルを用意

CREATE TABLE `test` (
   `col` JSON NULL DEFAULT NULL
);
INSERT INTO test (col) values (null);

■UPDATE分

testテーブルのcolカラムのkey1という要素を更新

UPDATE test SET
 col = JSON_SET(
   IFNULL(col, '{"key1":null}'),
   '$.key1',
   CAST('[{"name":"なまえ1","text":"テキスト1"}]' AS JSON)
 )

JSON_SETはnullの場合、値の設定が出来ません。
その為、IFNULLを使用してnullの場合にデフォルト値を使用するようにしています。

json文字列を設定する場合、CASTが必要になります。
json文字列を直接記述していますが、JSON_OBJECTを使用することも可能です。
※プログラムで文字列を生成することを考えています。

■PHPから更新

testテーブルのcolカラムのkey1という要素を更新

// SQL文字列を作成
$sql = sprintf(
    'UPDATE test SET col = JSON_SET(IFNULL(col, \'%s\'), \'$.key1\', CAST(\'%s\' AS JSON))',
    json_encode(['key1' => null]),
    json_encode(['name' => 'テスト'], JSON_UNESCAPED_UNICODE),
);
// SQLを実行
...

IFNULLのデフォルト値は（複数）階層になりますので、json_encodeで配列を渡しています。
設定値のjson_encodeのJSON_UNESCAPED_UNICODEは設定しないとエスケープされます。