ElasticSearchの検索クエリに関して(基礎編) の続き
#複数条件の検索クエリ
使用するデータはここで定義した、商品売り上げログ。
##基本形
{
"query": {
"bool": {
"filter": ...,
"must": ...,
"must_not": ..,
"should": ...,
}
}
}
- must (AND条件)
- filter (AND条件)
- should (OR条件)
- must_not (NOT条件)
AND条件
複数の条件が存在するとき全ての条件を満たすdocumentを検索。
filterクエリ
AかつBといった検索条件を満たすためのクエリ。
検索結果から計算されるスコア(score)を計算しない。
特にスコアの結果が必要でない限りfilterクエリを使ったほうが良いと思われる。
スコア(score)が具体的にどのようなものかはここでは省略。
"filter": [
{
{{基本的な検索クエリ または boolクエリ}}
},
{
{{基本的な検索クエリ または boolクエリ}}
},
...
]
例:filterクエリサンプル、以下の条件を満たすクエリ
- productが「杉のこ村」、「キノコの山」、
- placeが「東スーパー」
- @timestampが2019-06-28以上2019-06-29未満
GET user_price_index_*/_search
{
"size": 3,
"query": {
"bool": {
"filter": [
{
"terms": {
"product.keyword": ["杉のこ村","キノコの山"]
}
},
{
"term": {"place.keyword": "東スーパー"}
},
{
"range": {
"@timestamp": {
"time_zone": "Asia/Tokyo",
"gte": "2019-06-28T00:00:00",
"lt": "2019-06-29T00:00:00"
}
}
}
]
}
}
}
検索結果
{
"took": 17,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 140,
"failed": 0
},
"hits": {
"total": 9,
"max_score": 0,
"hits": [
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "7hmIxGoBzDMFYAVDcLu0",
"_score": 0,
"_source": {
"user_id": 50,
"kind_name": "購入",
"price": 120,
"place": "東スーパー",
"total_price": 480,
"total_price_with_tax": 518.4,
"product": "キノコの山",
"p_value": 4,
"@timestamp": "2019-06-28T09:15:35+09:00:00"
}
},
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "7xmIxGoBzDMFYAVDcLu0",
"_score": 0,
"_source": {
"user_id": 50,
"kind_name": "購入",
"price": 111,
"place": "東スーパー",
"total_price": 222,
"total_price_with_tax": 239.76,
"product": "杉のこ村",
"p_value": 2,
"@timestamp": "2019-06-28T09:15:35+09:00:00"
}
},
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "-BmIxGoBzDMFYAVDcLm0",
"_score": 0,
"_source": {
"user_id": 73,
"kind_name": "購入",
"price": 111,
"place": "東スーパー",
"total_price": 222,
"total_price_with_tax": 239.76,
"product": "杉のこ村",
"p_value": 2,
"@timestamp": "2019-06-28T05:51:52+09:00:00"
}
}
]
}
}
mustクエリ
AかつBといった検索条件を満たすためのクエリ。
検索結果から計算されるスコア(score)を計算する。
"must": [
{
{{基本的な検索クエリ または boolクエリ}}
},
{
{{基本的な検索クエリ または boolクエリ}}
},
...
]
例:mustクエリサンプル、以下の条件を満たすクエリ
- productが「杉のこ村」、「キノコの山」、
- placeが「東スーパー」
- @timestampが2019-06-28以上2019-06-29未満
GET user_price_index_*/_search
{
"size": 3,
"query": {
"bool": {
"must": [
{
"terms": {
"product.keyword": ["杉のこ村","キノコの山"]
}
},
{
"term": {"place.keyword": "東スーパー"}
},
{
"range": {
"@timestamp": {
"time_zone": "Asia/Tokyo",
"gte": "2019-06-28T00:00:00",
"lt": "2019-06-29T00:00:00"
}
}
}
]
}
}
}
検索結果
{
"took": 47,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 140,
"failed": 0
},
"hits": {
"total": 9,
"max_score": 3.856298,
"hits": [
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "-BmIxGoBzDMFYAVDcLm0",
"_score": 3.856298,
"_source": {
"user_id": 73,
"kind_name": "購入",
"price": 111,
"place": "東スーパー",
"total_price": 222,
"total_price_with_tax": 239.76,
"product": "杉のこ村",
"p_value": 2,
"@timestamp": "2019-06-28T05:51:52+09:00:00"
}
},
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "3BmGxGoBzDMFYAVDLKv-",
"_score": 3.8137383,
"_source": {
"user_id": 58,
"kind_name": "購入",
"price": 111,
"place": "東スーパー",
"total_price": 111,
"total_price_with_tax": 119.88,
"product": "杉のこ村",
"p_value": 1,
"@timestamp": "2019-06-28T03:24:49+09:00:00"
}
},
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "QxmIxGoBzDMFYAVDcMG5",
"_score": 3.8137383,
"_source": {
"user_id": 54,
"kind_name": "購入",
"price": 120,
"place": "東スーパー",
"total_price": 120,
"total_price_with_tax": 129.6,
"product": "キノコの山",
"p_value": 1,
"@timestamp": "2019-06-28T00:01:06+09:00:00"
}
}
]
}
}
OR条件
複数の条件が存在するときいずれかの満たすdocumentを検索。
shouldクエリ
OR検索が可能なクエリ。
minimum_should_matchで、documentが表示される最低条件数を設定できる。1
"should": [
{
{{基本的な検索クエリ または boolクエリ}}
},
{
{{基本的な検索クエリ または boolクエリ}}
},
...
]
例:shouldクエリサンプル、以下の検索条件を最低2個以上満たしたレコードを検索するクエリ
- productが「杉のこ村」にマッチする
- total_priceが300以上10000以下
- placeが「南商店」にマッチする
GET user_price_index_*/_search
{
"size": 3,
"query": {
"bool": {
"should": [
{
"term": {
"product.keyword": "杉のこ村"
}
},
{
"range": {
"total_price": {
"gte": 300,"lte": 10000
}
}
},
{
"term": {
"place.keyword": "南商店"
}
}
],"minimum_should_match": 2
}
}
}
検索結果
{
"took": 1045,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 1280,
"max_score": 5.2101765,
"hits": [
{
"_index": "user_price_index_20190601",
"_type": "_doc",
"_id": "IhmGxGoBzDMFYAVDLKb5",
"_score": 5.2101765,
"_source": {
"user_id": 43,
"kind_name": "購入",
"price": 111,
"place": "南商店",
"total_price": 555,
"total_price_with_tax": 599.4,
"product": "杉のこ村",
"p_value": 5,
"@timestamp": "2019-06-01T17:50:57+09:00:00"
}
},
{
"_index": "user_price_index_20190604",
"_type": "_doc",
"_id": "ShmHxGoBzDMFYAVDqrUM",
"_score": 5.080102,
"_source": {
"user_id": 56,
"kind_name": "購入",
"price": 111,
"place": "南商店",
"total_price": 555,
"total_price_with_tax": 599.4,
"product": "杉のこ村",
"p_value": 5,
"@timestamp": "2019-06-04T20:21:35+09:00:00"
}
},
{
"_index": "user_price_index_20190605",
"_type": "_doc",
"_id": "DxmIxGoBzDMFYAVDcLy0",
"_score": 4.9283595,
"_source": {
"user_id": 9,
"kind_name": "購入",
"price": 111,
"place": "南商店",
"total_price": 444,
"total_price_with_tax": 479.52,
"product": "杉のこ村",
"p_value": 4,
"@timestamp": "2019-06-05T10:01:16+09:00:00"
}
}
]
}
}
NOT条件
指定した検索条件の否定を満たすdocumentを検索。
must_notクエリ
"must_not": [
{
{{基本的な検索クエリ または boolクエリ}}
},
{
{{基本的な検索クエリ または boolクエリ}}
},
...
]
例:must_notクエリサンプル、以下の検索条件をすべて否定するログを検索するクエリ。
- kind_nameが「アクセス」
- productが「杉のこ村」
- placeが「北デパート」
- placeが「西コンビニ」
- placeが「南商店」
GET user_price_index_*/_search
{
"size": 3,
"query": {
"bool": {
"must_not": [
{
"term":{
"kind_name.keyword":"アクセス"
}
},
{
"term": {
"product.keyword": "杉のこ村"
}
},
{
"term": {
"place.keyword": "北デパート"
}
},
{
"term": {
"place.keyword": "西コンビニ"
}
},
{
"term": {
"place.keyword": "南商店"
}
}
]
}
}
}
検索結果
{
"took": 85,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 969,
"max_score": 1,
"hits": [
{
"_index": "user_price_index_20190601",
"_type": "_doc",
"_id": "uhmHxGoBzDMFYAVDqq0K",
"_score": 1,
"_source": {
"user_id": 76,
"kind_name": "購入",
"price": 92,
"place": "東スーパー",
"total_price": 92,
"total_price_with_tax": 99.36,
"product": "コアラのマーチ",
"p_value": 1,
"@timestamp": "2019-06-01T13:24:59+09:00:00"
}
},
{
"_index": "user_price_index_20190601",
"_type": "_doc",
"_id": "oRmHxGoBzDMFYAVDqrUM",
"_score": 1,
"_source": {
"user_id": 59,
"kind_name": "購入",
"price": 140,
"place": "東スーパー",
"total_price": 700,
"total_price_with_tax": 756,
"product": "たけのこの里",
"p_value": 5,
"@timestamp": "2019-06-01T22:19:17+09:00:00"
}
},
{
"_index": "user_price_index_20190602",
"_type": "_doc",
"_id": "WBmGxGoBzDMFYAVDLKv9",
"_score": 1,
"_source": {
"user_id": 16,
"kind_name": "購入",
"price": 140,
"place": "東スーパー",
"total_price": 140,
"total_price_with_tax": 151.2,
"product": "たけのこの里",
"p_value": 1,
"@timestamp": "2019-06-02T11:25:50+09:00:00"
}
}
]
}
}
#さらに複雑な検索クエリ
上記クエリを組み合わせることで、さらに複雑な条件の検索が可能。
以下で2点ほど例を紹介する。
##例1 複数条件のうち1つを満たすクエリ
例:以下の条件のうち片方を満たすログを求めるクエリ
- user_idが「39」かつkind_nameが「アクセス」かつplaceが「東スーパー」
- user_idが「59」かつkind_nameが「購入」かつplaceが「南商店」かつproductが「コアラのマーチ」
GET user_price_index_*/_search
{
"size": 5,
"query": {
"bool": {
"should": [
{
"bool": {
"filter": [
{
"term": {
"user_id": 39
}
},
{
"term": {
"kind_name.keyword": "アクセス"
}
},
{
"term": {
"place.keyword": "東スーパー"
}
}
]
}
},
{
"bool": {
"filter": [
{
"term": {
"user_id": 59
}
},
{
"term": {
"kind_name.keyword": "購入"
}
},
{
"term": {
"place.keyword": "南商店"
}
},
{
"term": {
"product.keyword": "コアラのマーチ"
}
}
]
}
}
]
}
}
}
検索結果
{
"took": 304,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 12,
"max_score": 0,
"hits": [
{
"_index": "user_price_index_20190611",
"_type": "_doc",
"_id": "tRmGxGoBzDMFYAVDLKT4",
"_score": 0,
"_source": {
"user_id": 39,
"kind_name": "アクセス",
"place": "東スーパー",
"@timestamp": "2019-06-11T18:45:05+09:00:00"
}
},
{
"_index": "user_price_index_20190620",
"_type": "_doc",
"_id": "OhmGxGoBzDMFYAVDLKn9",
"_score": 0,
"_source": {
"user_id": 39,
"kind_name": "アクセス",
"place": "東スーパー",
"@timestamp": "2019-06-20T13:35:10+09:00:00"
}
},
{
"_index": "user_price_index_20190623",
"_type": "_doc",
"_id": "ZxmHxGoBzDMFYAVDqq8K",
"_score": 0,
"_source": {
"user_id": 39,
"kind_name": "アクセス",
"place": "東スーパー",
"@timestamp": "2019-06-23T15:20:09+09:00:00"
}
},
{
"_index": "user_price_index_20190628",
"_type": "_doc",
"_id": "QhmHxGoBzDMFYAVDqrcM",
"_score": 0,
"_source": {
"user_id": 59,
"kind_name": "購入",
"price": 92,
"place": "南商店",
"total_price": 368,
"total_price_with_tax": 397.44,
"product": "コアラのマーチ",
"p_value": 4,
"@timestamp": "2019-06-28T14:22:57+09:00:00"
}
},
{
"_index": "user_price_index_20190629",
"_type": "_doc",
"_id": "ARmHxGoBzDMFYAVDqq8K",
"_score": 0,
"_source": {
"user_id": 39,
"kind_name": "アクセス",
"place": "東スーパー",
"@timestamp": "2019-06-29T02:53:57+09:00:00"
}
}
]
}
}
##例2 複数条件すべてを満たすクエリ
例:以下の条件のうち両方を満たすログを求めるクエリ
- placeが「東スーパー」またはproductが「コアラのマーチ」
- total_priceが300以上
GET user_price_index_*/_search
{
"size": 5,
"query": {
"bool": {
"filter": [
{
"bool": {
"should": [
{
"term": {
"place.keyword": "東スーパー"
}
},
{
"term": {
"product.keyword": "コアラのマーチ"
}
}
]
}
},
{
"range": {
"total_price": {
"gte": 300
}
}
}
]
}
}
}
検索結果
{
"took": 17,
"timed_out": false,
"_shards": {
"total": 145,
"successful": 145,
"skipped": 0,
"failed": 0
},
"hits": {
"total": 1066,
"max_score": 0,
"hits": [
{
"_index": "user_price_index_20190601",
"_type": "_doc",
"_id": "RhmGxGoBzDMFYAVDLKz-",
"_score": 0,
"_source": {
"user_id": 4,
"kind_name": "購入",
"price": 92,
"place": "北デパート",
"total_price": 368,
"total_price_with_tax": 397.44,
"product": "コアラのマーチ",
"p_value": 4,
"@timestamp": "2019-06-01T13:18:14+09:00:00"
}
},
{
"_index": "user_price_index_20190601",
"_type": "_doc",
"_id": "txmHxGoBzDMFYAVDqq8K",
"_score": 0,
"_source": {
"user_id": 81,
"kind_name": "購入",
"price": 92,
"place": "北デパート",
"total_price": 368,
"total_price_with_tax": 397.44,
"product": "コアラのマーチ",
"p_value": 4,
"@timestamp": "2019-06-01T11:50:54+09:00:00"
}
},
{
"_index": "user_price_index_20190601",
"_type": "_doc",
"_id": "oRmHxGoBzDMFYAVDqrUM",
"_score": 0,
"_source": {
"user_id": 59,
"kind_name": "購入",
"price": 140,
"place": "東スーパー",
"total_price": 700,
"total_price_with_tax": 756,
"product": "たけのこの里",
"p_value": 5,
"@timestamp": "2019-06-01T22:19:17+09:00:00"
}
},
{
"_index": "user_price_index_20190602",
"_type": "_doc",
"_id": "wRmGxGoBzDMFYAVDLKb5",
"_score": 0,
"_source": {
"user_id": 26,
"kind_name": "購入",
"price": 92,
"place": "北デパート",
"total_price": 460,
"total_price_with_tax": 496.8,
"product": "コアラのマーチ",
"p_value": 5,
"@timestamp": "2019-06-02T00:41:02+09:00:00"
}
},
{
"_index": "user_price_index_20190602",
"_type": "_doc",
"_id": "1RmIxGoBzDMFYAVDcLez",
"_score": 0,
"_source": {
"user_id": 12,
"kind_name": "購入",
"price": 120,
"place": "東スーパー",
"total_price": 360,
"total_price_with_tax": 388.8,
"product": "キノコの山",
"p_value": 3,
"@timestamp": "2019-06-02T14:46:34+09:00:00"
}
}
]
}
}
#ElasticSearchシリーズ
- [ElasticSearchの検索クエリに関して(基礎編)] (https://qiita.com/horankey_jet_city/private/97d41c275434be897d58)
- ElaticSearchの検索クエリに関して(応用編)
- [ElasticSearchの集約クエリに関して(基礎編)] (https://qiita.com/horankey_jet_city/private/94eda5046a8fe1febe45)
- ElasticSearchの集約クエリに関して(応用?編)
-
デフォルトは1 ↩