MongoDBでLIKE検索
MongoDBで、MySQLでいうLIKEのようなクエリの書き方を紹介します。
〜を含む
db.col.find({field: {$regex: "something"}});
fieldの値にsomethingが含まれるコレクションを取得します。
〜を含む(大文字・小文字区別しない)
db.col.find({field: {$regex: "something", $options: "i"}});
fieldの値に、大文字・小文字を問わずsomethingが含まれるコレクションを取得します。
サンプル
以下のようなデータが入っているテーブルがあるとします。
> db.sample_log.find({},{_id:0});
{ "uid" : "hoge", "device" : "xxx", "created_at" : "2019-02-01 00:00:00" }
{ "uid" : "fuga", "device" : "yyy", "created_at" : "2019-02-01 00:00:01" }
{ "uid" : "hoge", "device" : "xxx", "created_at" : "2019-02-01 00:00:02" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01 00:00:03" }
{ "uid" : "hoge", "device" : "yyy", "created_at" : "2019-02-01 00:00:04" }
{ "uid" : "fuga", "device" : "yyy", "created_at" : "2019-02-01 00:00:05" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01 00:00:06" }
{ "uid" : "hoge", "device" : "yyy", "created_at" : "2019-02-02 00:00:04" }
{ "uid" : "fuga", "device" : "yyy", "created_at" : "2019-02-02 00:00:05" }
{ "uid" : "foo", "device" : "xxx", "created_at" : "2019-02-02 00:00:06" }
{ "uid" : "bar", "device" : "yyy", "created_at" : "2019-02-02 00:00:07" }
{ "uid" : "foo", "device" : "xxx", "created_at" : "2019-02-02 00:00:08" }
{ "uid" : "bar", "device" : "yyy", "created_at" : "2019-02-02 00:00:09" }
{ "uid" : "hoge", "device" : "xxx", "created_at" : "2019-02-03 00:00:09" }
{ "uid" : "fuga", "device" : "yyy", "created_at" : "2019-02-03 00:00:10" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-03 00:00:11" }
$regex
> db.sample_log.find({uid:{$regex:'yo'}},{_id:0});
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01 00:00:03" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01 00:00:06" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-03 00:00:11" }
$regex + $options: ‘i’
> db.sample_log.find({uid:{$regex:'YO'}},{_id:0});
>
何も付けないと大文字・小文字が区別されます。
> db.sample_log.find({uid:{$regex:'YO', $options: 'i'}},{_id:0});
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01 00:00:03" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-01 00:00:06" }
{ "uid" : "piyo", "device" : "zzz", "created_at" : "2019-02-03 00:00:11" }
$options: ‘i’をつけることで、大文字・小文字を区別しないで検索できます。