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の中身も部分一致検索出来るようになったのでとても便利です。
もしかしたら他にもっとスマートなやりかたはあるはず・・・
0 Comments