【MySQL】JSON型を使った配列の格納方法＆使いどころ

最近(?)のMySQLはJSON型が使えるようです。

▼ 今まで無理だった次のことが可能に

配列をそのまま格納する
オブジェクトを直接格納する
部分的にデータを書き換える

僕自身も使ってみて便利さに驚きました。

ただし使いどころには注意が必要です。

そういった使うべき場面の判別もふくめ、
MySQLでのJSON型の使用方法なんかをまとめます。

このページの目次

従来のMySQLは配列・オブジェクトは格納不可
MySQL5.7.8からJSON型が追加された
テーブルにJSON型を追加・挿入する方法
JSON型は内部的にはLONGTEXT型だが…
JSON型を部分的に更新（UPDATE）するには
JSON型で一部の値をインクリメントするには
いつJSON型を使うべき？使用すべき場面
逆にJSON型を使ってはいけない場面は？

従来のMySQLは配列・オブジェクトは格納不可

従来のMySQLは配列・連想配列は格納できません。

だから以前にこんな記事を書いたほどです。

▼ PHPからMySQLに配列を格納するには…４つの方法

PisukeCode - Web開発まとめ

PHPからMySQLに配列を格納するには...５つの方法

https://pisuke-code.com/php-mysql-ways-to-store-array

PHPからMySQLを使っていたとき・・どうしても配列を格納したいあれ？でもどうやって？ MySQLに配列型なんてないし...そう悩む場面がありました。でも工夫次第でなんとでもなります。そこで PHP+MySQLで配列格納する方法を２つ紹介！コード例と一緒に載せておきます。方法１．CSVとして疑似的な配列として格納まず１つめはCSVとして格納する方法※ CSV = Comma Separated ValuesCSVについての説明は不要ですよね。具体的な方法は次のような感じ１．テーブルを作る▼ 例として次のテーブルを作成CREATE TABLE group_list ( id...

４つも方法を考えたけど、JSONもあったのか…

書いてた当時はググってもJSON型があると知らなかったので、未実装だったか知名度がなかったのかもしれません。とにかく知らなかった

MySQL5.7.8からJSON型が追加された

要望が多かったのかもしれません。

ついにMySQL5.7.8からJSON型が追加されました。

▼ MySQL5.7.8リリースノート : JSON型とは？

JSON: MySQL now supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides these advantages over storing JSON-format strings in a string column:

Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.

Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits efficient access to document elements.

Along with the JSON data type, a set of SQL functions is available to enable operations on JSON values, such as creation, manipulation, and searching. In addition, the CONVERT() and CAST() functions can convert values between JSON and other types.

引用元　：　https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-8.html#mysqld-5-7-8-json

▼ 上記リリースノートの意訳

JSON: MySQLはJSON(JavaScript Object Notation)ドキュメントへの効率的なアクセスを可能にするJSONデータ形式をサポートしました。JSONデータ形式はJSON文字列を文字列カラムに格納するよりも、次のような利点があります。

- JSONカラムに格納されたJSONドキュメントの自動検証を行う。無効なドキュメントはエラーになる。

- 最適化された保存形式。JSONカラムに格納されたJSONドキュメントは、ドキュメント要素に効率的にアクセスできる内部フォーマットに変換される

JSONデータ型とともに、JSON値の作成、操作、検索などの操作ができる一連のSQL関数も用意されました。また、CONVERT()関数やCAST()関数は、JSONと他データ型とのキャストを行うことができます。

これがリリースされたのは2015年8月頃…

最近かと思ったけどずいぶん昔ですね。

その当時からJSONという選択肢はあったみたいです。

テーブルにJSON型を追加・挿入する方法

テーブルにJSON型を使う方法について。

例えば次のようなテーブルを作ります。

なにかの投票結果を記録するテーブル
各候補者の得票数をもつJSONを格納
JSONだから柔軟に構造を変化できる

言葉だと分かりにくいからSQLで示します。

▼ JSON型を持つテーブル作るSQL例

CREATE TABLE votes (
  id INT PRIMARY KEY,
  result_json JSON
)

CREATE TABLE votes (

id INT PRIMARY KEY,

result_json JSON

)

▼ テーブルにJSON型を挿入

insert into votes values(1, '{
  "Cupcake": 7,
  "Donut": 14, 
  "Eclair": 10,
  "KitKat": 23,
  "Marshmallow": 4
}');

insert into votes values(1, '{

"Cupcake": 7,

"Donut": 14,

"Eclair": 10,

"KitKat": 23,

"Marshmallow": 4

}');

このように型としてJSONが導入されました。JSON型に配列・オブジェクトを格納したい時は、そのまま文字列として突っこめばいいだけです。

この例だと「好きなお菓子アンケート」みたいなケースを想定していて、JSON型を使うとカラムの拡張なしで柔軟に投票結果を記録できます。

そういう柔軟さがJSONの魅力です。

JSON型は内部的にはLONGTEXT型だが…

それでできあがったテーブル構造について

こんな風になってました。

内部的には LONGTEXT として扱われてます。

ただしJSON型は特別なテキストであり、構文エラーがあると挿入に失敗します。たとえば二重引用符がなかったり、コンマの付け忘れなどですね。

※ あとJSON操作関数も沢山ある（後述）

そこがTEXT型との大きな差異です。

JSON型を部分的に更新（UPDATE）するには

JSON型による最大のメリットがここにあります。

「一部データの部分的更新ができること」

これこそがJSON型を使う理由です。

▼ 例えばこういうケースを考える

既にJSON型にはオブジェクトが入ってる
その中の特定のキーの値だけ更新したい
その中の一部の値だけinc/decさせたい

それが簡単にできちゃうのがJSON型のすごさです。

たとえば先ほどの例で考えてみます。

▼ 既にこういうデータが入っている

insert into votes values(1, '{
  "Cupcake": 7,
  "Donut": 14, 
  "Eclair": 10,
  "KitKat": 23,
  "Marshmallow": 4
}');

insert into votes values(1, '{

"Cupcake": 7,

"Donut": 14,

"Eclair": 10,

"KitKat": 23,

"Marshmallow": 4

}');

ここで "Oreo": 19 をオブジェクトに追加したい場合はどうするかです。JSON型以外なら一度レコード取得 ⇒ JSON解析・書き換え ⇒ 全部を更新みたいな大変な処理になります。

JSON型なら部分更新がめちゃくちゃ楽です。

▼ こういう感じで部分更新できる

UPDATE votes SET result_json 
  = JSON_SET(result_json,'$.Oreo',19)
WHERE id = 1;

UPDATE votes SET result_json

= JSON_SET(result_json,'$.Oreo',19)

WHERE id = 1;

JSON操作系関数の中では、自身のキーを表すのに $.hoge みたいな記法が使用可能。もし２階層なら $.hoge.fuga みたいな記述です。

またJSON挿入・更新系の関数は３つあります。

JSON_SET()
既存の値なら置換、存在しない値なら追加
JSON_INSERT()
既存の値を置換せずに値を挿入する
JSON_REPLACE()
既存の値のみを置き換えする

引数の数・種類は３つとも同様です。

JSON型で一部の値をインクリメントするには

それから部分的なインクリメントも可能。

キーが存在しないなら 0 の値で挿入
キーが存在するなら既存値をインクリメント

こういう処理を行うにはSQLで工夫が必要です。

▼ 例えばこういうSQL例

UPDATE votes SET result_json = JSON_SET(
    result_json, '$.Cupcake', 
    IF(
       JSON_CONTAINS_PATH(result_json, 'one', '$.Cupcake') > 0,
       JSON_EXTRACT(result_json, '$.Cupcake')+1,
       0
	)
) 
WHERE id = 1;

UPDATE votes SET result_json = JSON_SET(

result_json, '$.Cupcake',

IF(

JSON_CONTAINS_PATH(result_json, 'one', '$.Cupcake') > 0,

JSON_EXTRACT(result_json, '$.Cupcake')+1,

)

WHERE id = 1;

部分更新に JSON_SET() を使うのは変わらないけど、キーが存在するかしないかを JSON_CONTAINS_PATH() で判断してます。存在するなら JSON_EXTRACT() から既存の値をインクリメント、存在しないなら新規に挿入という流れです。

部分インクリメントのやり方は以上

いつJSON型を使うべき？使用すべき場面

それでJSON型はいつ使うべきかという話

次がJSON型の使いどころと考えてます。

データ種類が大量にあるケース、
データ種類が動的に変化するケース
静的なカラムだけでは無理なケース

より具体的な例を挙げるなら…

何かの投票に対する得票数の結果
アンケートの任意に追加できる項目
あまり数の多くないタグ管理など

例えば先ほどのvotesテーブルの場合…「好きなお菓子アンケート投票」みたいに任意にお菓子名（キー名）を追加できるシーンを想定してます。

動的・拡張性が必要ならJSONの使いどころです。

逆にJSON型を使ってはいけない場面は？

逆にJSON型を使うべきでない場面について。

少なくとも乱用するのは避けるべきです。

僕自身はこのように考えてます。

データ種類が初めから有限な場合
あとからデータ種類が増えない場合
テーブル連結で事足りる場合

普通の用途ならJSON型は必要ありません。

データ種類（カラム）が有限なら、JSON型を使うのはムダでしかない。最適化されてると言っても、LONGTEXT型なので相応の時間はかかります。

もし動的にデータ種類が増えないなら、テーブル細分化してテーブル連結（ LEFT JOIN など）するのが一番すぐれた方法です。

以上、MySQL JSON型についてでした。ではまた