MySQLでJSONデータを部分一致検索する

MySQLは5.7からJSON型に対応していたり、JSONを操作する関数が追加されています。

既存のtext型とかの列に入っているjsonデータもあれこれ出来るみたいなので、今回はデータ内のJSONからキー名を指定して部分一致する機能を作りたいと思いました。

調べてみるとJSON_CONTAINSという関数が使えるっぽくて色々試してみたのですが、どうやら思っていることが出来ないんですよね。

SELECT * FROM xxx WHERE JSON_EXTRACT(json列名, ‘ABC‘, $.キー名’)

SELECT * FROM xxx WHERE JSON_EXTRACT(json列名, ‘あああ‘, $.キー名’)

上の例だと検索したい対象のデータが「ABC」でも検索に引っかからなくて、よく調べてみると検索文字列の部分を「'”ABC”‘」にすると検索出来るんですよね。

下の例だとデータは「あああ」ではなく「”\u3042\u3042\u3042″」で入っているのでこのままじゃダブルクォーテーションを入れてもひっかかりません。

そして何より部分一致じゃないです。

この列がlongtext型なのが問題でJSON型の列なら問題ないのかもしれませんが、今回は型を変えたくないのでどうにかして対応したいと思います。

一度対象データを抽出してから検索してみる

JSON_EXTRACTでまずは対象のデータを取り出して、そこに通常のLIKE検索をかけるという方法で対応しました。

しかし、JSON_EXTRACTだけではダブルクォーテーションが残っているし2バイト文字がエンコードされたままなのでJSON_UNQUOTEで元に戻します。

SELECT * FROM xxx WHERE JSON_UNQUOTE(JSON_EXTRACT(json列名,’$.キー名’)) LIKE ‘%検索文字列%'”

これで抽出したデータに対してLIKE検索がかけられました。

無理やりですがJSONの中身も部分一致検索出来るようになったのでとても便利です。

もしかしたら他にもっとスマートなやりかたはあるはず・・・