あかつきのお宿

MySQL5.7以降の話なんですが、フィールドにJSON型という型を指定できるようになりました。文字通りにJSON形式でデータを保存できる型です。

TEXT型でも自分でJSON形式に直してデータを突っ込めばJSONデータを保存できるのですが、TEXT型と違ってJSON型の場合は専用の関数がいくつか使えるというメリットがあります。

JSON_CONTAINSについて

例えばこんな感じのJSONデータが入ってるとします。

+----+-----------------------+
| id |          json         |
+----+-----------------------+
|  1 | ["aaa", "bbb", "ccc"] |
|  2 | ["aaa", "bbb"]        |
|  3 | ["bbb", "ccc"]        |
|  4 | [111, 222, 333]       |
+----+-----------------------+

これをSELECT文で検索したいとき、TEXT型だとLIKE検索とか使ってJSONデータを文字列として検索する感じになるかと思います。

例えば「aaa」と「bbb」というデータを持っているレコードだけ抽出したければこんな感じですかね。

SELECT * FROM table WHERE `json` LIKE '%"aaa", "bbb"%'

だいぶ雑なSQLですけどこれでidが1と2のデータを抽出できます。しかしあくまでもこれは「”aaa”, “bbb”」という文字列として検索しているので、例えばここで「aaa」と「ccc」を持っているデータを取ってこようと思っても同じ書き方では抽出できません。AND検索などをする必要がありますね。

// これだとダメ
SELECT * FROM table WHERE `json` LIKE '%"aaa", "ccc"%'

// これならid1のデータが取れる
SELECT * FROM table WHERE `json` LIKE '%aaa%' AND `json` LIKE '%ccc%'

まあこれでも取れるっちゃ取れるんでこれをやればええやんって話ではあるんですけど、もしJSONの中身がもっと要素の数が多くて複雑な多重配列みたいなデータだったりしたらWHERE句の作成も大変ですよね。

でもJSON型であればこんな感じのSQL文でaaaとcccを持つデータを抽出できます。

SELECT * FROM table WHERE JSON_CONTAINS(`json`, '["aaa", "ccc"]')

このようにJSON_CONTAINSという関数を使えば指定したリストの要素を全て持っているデータだけを抽出できます。この関数は完全一致ではなく部分一致なので検索結果としてはLIKE検索と同じような感じになります。だからaaaとbbbで検索した場合は上記と同じように1と2のデータが引っかかるってことですね。

// id1と2が該当する
SELECT * FROM table WHERE JSON_CONTAINS(`json`, '["aaa", "bbb"]')

完全一致用の関数がない気がする

LIKE検索のようなことがしたいのであれば上記の書き方で良いんですが、こっちで指定した要素だけを持つデータを抽出したい場合、つまり完全一致でデータを取得したい場合、JSON_CONTAINSでは無理なので他の方法で取得することになります。

が、僕が試した限りでは、完全一致でデータを取る関数っていうのがないんですよ。

例えば[“aaa”, “bbb”]だけを持つデータを取りたい場合。上記のデータで言うとid2だけを取りたいってことですね。これができないっぽいんですよね。

ドキュメントを調べてみるとJSON_CONTAINS以外の取得方法としてJSON_EXTRACTとかJSON_OVERLAPS（MySQL8.0以降で使用可能）などがあるんですが、いずれの方法でも完全一致で取得することはできませんでした。

MySQLのJSON関数リファレンス

じゃあ完全一致で取得することは不可能なのかって話なんですが、MySQLのJSON関数の中には要素の数でデータを抽出できるJSON_LENGTHという関数があります。

// id2と3が該当する
SELECT * FROM table WHERE JSON_LENGTH(`json`) = 2

// id1と4が該当する
SELECT * FROM table WHERE JSON_LENGTH(`json`) = 3

これで要素の数が条件と一致するデータだけを取得できるので、このJSON_LENGTHとJSON_CONTAINSを組み合わせればid2のデータだけを抽出できます。

SELECT * FROM table WHERE JSON_CONTAINS(`json`, '["aaa", "bbb"]') AND JSON_LENGTH(`json`) = 2

こんな感じですね。[“aaa”, “bbb”]の要素を持っていて、なおかつ要素の数が2つのデータだけを抽出するというSELECT文です。

一応これで取れるから良いっちゃ良いんですけど、でも完全一致の需要って絶対あるはずだし、完全一致の関数があっても良さそうなもんですけどね。いずれ実装されるんでしょうか？

おまけ（連想配列の場合）

JSON_CONTAINSやJSON_LENGTHは連想配列にも使用できます。例えばJSONデータの中身をこんな風に変えてみましょう。

+----+-----------------------------------------------------+
| id |                         json                        |
+----+-----------------------------------------------------+
|  1 | {"aaa":[111, 222], "bbb":[111, 222, 333], "ccc":333}|
|  2 | {"aaa":[111, 333], "bbb":[123, 456]}                |
|  3 | {"bbb":[111, 222], "ccc":123}                       |
+----+-----------------------------------------------------+

このとき、aaaというキーの中に111という要素があるレコードだけを取得したい場合はこんな風に書きます。

// id1と2が該当する
SELECT * FROM table WHERE JSON_CONTAINS(`json`, '111', '$.aaa')

// id1が該当する
SELECT * FROM table WHERE JSON_CONTAINS(`json`, '[111, 222]', '$.aaa')

// id3が該当する
SELECT * FROM table WHERE JSON_CONTAINS(`json`, '123', '$.ccc')

// id2と3が該当する
SELECT * FROM table WHERE JSON_LENGTH(`json`, '$.bbb') = 2

JSON_CONTAINSの第三引数やJSON_LENGTHの第二引数に「$.キー」を指定することで、そのキーに含まれる要素で検索してくれます。

あるいはJSON_CONTAINSの第二引数に直接連想配列の形で書いても取得できます。

// id1と2が該当する
SELECT * FROM table WHERE JSON_CONTAINS(`json`, '{"aaa":[111]}')