MySQLでJSON

MySQL5.7 から JSON のカラムが使えるようになりましたので色々と試してみました。以下では #K#jcol をJSON型のカラムとして説明します。

### title
UPDATE

###title3
JSON_SET

###source
UPDATE tbl SET jcol=JSON_SET(jcol, '$.type', 'hogehoge') WHERE id=1

###memo
tbl(テーブル) の id=1 の jcol(JSON型カラム) の type(JSONのキー名) に 'hogehoge' を設定します。 つまり以下のようなJSONになります。

###source
(結果)
SELECT jcol FROM tbl WHERE id=1
// 結果
{"type":"hogehoge"}

### memo
JSON_SET を使わず、直接文字列 で更新することもできます。
###source
UPDATE tbl SET jcol='{"type":"hogehoge"}' WHERE id=1

###title3
JSON_REMOVE

###memo
JSON_REMOVE で JSONのキーを削除。
###source
UPDATE tbl SET jcol=JSON_REMOVE(jcol, '$.type') ;

###title3
注意点(UPDATEの空振り?)

###memo mini
MySQL 5.7.18 の場合、jcol(JSONカラム) の初期値が NULL や 空 だとUPDATE しても #R#反映されません でした。またデフォルト値の設定もできませんでした。ので、 初回 INSERT時に jcol に '{}' を設定しています。

### memo
もしくは・・・ JSON_INSERT と JSON_UPDATE 同時にやると空振りはなくなるかも・・・

###source
UPDATE tbl SET 
jcol=JSON_SET(jcol, '$.$k.$u',1) ,
jcol=JSON_INSERT(jcol, '$.$k', JSON_OBJECT('$u', 1)) 
WHERE id='$id' " ;

### title
SELECT

### memo
以下のようなデーターがある場合。
###source
SELECT jcol FROM tbl WHERE id=1
// 結果
{"type":"hogehoge"}

###source
SELECT jcol->'$.type' FROM tbl WHERE id=1
// 結果
"hogehoge"

### memo
これだと #*#"hogehoge" とダブルクォーテーションがついてくるので

###source
SELECT jcol->>'$.type' FROM tbl WHERE id=1
// 結果
hogehoge

### memo
#*#->> にすることで #*#hogehoge というダブルクォーテーション無しの値が取得できます。

###title3
WHERE句+JSON型カラム

###source
SELECT * FROM tbl WHERE jcol->'$.type'='hogehoge'

SELECT jcol->>'$.type' as type FROM tbl WHERE jcol->'$.type'='hogehoge'

###title3
JSON_INSERT

###source
UPDATE tbl SET jcol=JSON_INSERT(jcol, '$.fav', JSON_OBJECT('a', 1)) WHERE id=1

### title
PHP でJSONを扱う

### memo

PHP には json_encode や json_decode といった関数があります。MySQLから取得したJSONデーターを #*#json_decode すれば #*#連想配列 として扱いやすくなります。

### source
(php JSONを連想配列に)
$arr = json_decode($json, true) ;
print_r($arr) ;

wakatta 記法