ElaticSearchの検索クエリに関して（応用編）

[ElasticSearchの検索クエリに関して（基礎編）](https://qiita.com/horankey_jet_city/private/97d41c275434be897d58) の続き


#複数条件の検索クエリ

使用するデータは[ここ](https://qiita.com/horankey_jet_city/private/97d41c275434be897d58#%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%83%AD%E3%82%B0)で定義した、商品売り上げログ。


##基本形

```json:複数条件の検索クエリ
{
  "query": {
    "bool": {
      "filter": ..., 
      "must": ...,
      "must_not": ..,
      "should": ...,
    }
  }
}
```

- must   (AND条件)
- filter (AND条件)
- should  (OR条件)
- must_not (NOT条件)



## AND条件

複数の条件が存在するとき全ての条件を満たす**document**を検索。



### filterクエリ

AかつBといった検索条件を満たすためのクエリ。
検索結果から計算されるスコア(score)を計算しない。
特にスコアの結果が必要でない限り**filter**クエリを使ったほうが良いと思われる。
スコア(score)が具体的にどのようなものかはここでは省略。

```json:filterクエリ
"filter": [
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  ...
]
```


例：filterクエリサンプル、以下の条件を満たすクエリ

- productが「杉のこ村」、「キノコの山」、
- placeが「東スーパー」
- @timestampが2019-06-28以上2019-06-29未満

```json:リクエスト(filterクエリサンプル)
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"
            }
          }
        }
      ]
    }
  }
}
```



<details><summary>検索結果</summary><div>

```json:検索結果(filterクエリサンプル)
{
  "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"
        }
      }
    ]
  }
}
```
</div></details>

### mustクエリ

AかつBといった検索条件を満たすためのクエリ。
検索結果から計算されるスコア(score)を計算する。

```json:mustクエリ
"must": [
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  ...
]
```


例：mustクエリサンプル、以下の条件を満たすクエリ

- productが「杉のこ村」、「キノコの山」、
- placeが「東スーパー」
- @timestampが2019-06-28以上2019-06-29未満

```json:リクエスト(mustクエリサンプル)
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"
            }
          }
        }
      ]
    }
  }
}

```


<details><summary>検索結果</summary><div>


```json:検索結果(mustクエリサンプル)

{
  "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"
        }
      }
    ]
  }
}

```
</div></details>

## OR条件

複数の条件が存在するときいずれかの満たす**document**を検索。

### shouldクエリ

OR検索が可能なクエリ。
**minimum_should_match**で、**document**が表示される最低条件数を設定できる。[^1]

```json:shouldクエリ
"should": [
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  ...
]
```


例：shouldクエリサンプル、以下の検索条件を最低2個以上満たしたレコードを検索するクエリ

- productが「杉のこ村」にマッチする
- total_priceが300以上10000以下
- placeが「南商店」にマッチする


```json:リクエスト(shouldクエリサンプル)
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
    }
  }
  
}
```


<details><summary>検索結果</summary><div>



```json:検索結果(shouldクエリサンプル)
{
  "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"
        }
      }
    ]
  }
}
```
</div></details>

## NOT条件
指定した検索条件の否定を満たす**document**を検索。
### must_notクエリ

```json:must_notクエリ
"must_not": [
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  ...
]
```



例：must_notクエリサンプル、以下の検索条件をすべて否定するログを検索するクエリ。

- kind_nameが「アクセス」
- productが「杉のこ村」
- placeが「北デパート」
- placeが「西コンビニ」
- placeが「南商店」


```json:リクエスト(must_notクエリサンプル)
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": "南商店"
          }
        }
      ]
    }
  }
}
```


<details><summary>検索結果</summary><div>

```json:検索結果(must_notクエリサンプル)
{
  "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"
        }
      }
    ]
  }
}
```
</div></details>





#さらに複雑な検索クエリ


上記クエリを組み合わせることで、さらに複雑な条件の検索が可能。

以下で2点ほど例を紹介する。



##例1 複数条件のうち1つを満たすクエリ

例：以下の条件のうち片方を満たすログを求めるクエリ

- user_idが「39」かつkind_nameが「アクセス」かつplaceが「東スーパー」
- user_idが「59」かつkind_nameが「購入」かつplaceが「南商店」かつproductが「コアラのマーチ」


```json:リクエスト(例1)
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": "コアラのマーチ"
                }
              }
            ]
          }
        }
      ]
    }
  }
}
```

<details><summary>検索結果</summary><div>

```json:検索結果(例1)
{
  "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"
        }
      }
    ]
  }
}
```
</div></details>

##例2 複数条件すべてを満たすクエリ

例：以下の条件のうち両方を満たすログを求めるクエリ

- placeが「東スーパー」またはproductが「コアラのマーチ」
- total_priceが300以上

```json:リクエスト(例2)
GET user_price_index_*/_search
{
  "size": 5,
  "query": {
    "bool": {
      "filter": [
        {
          "bool": {
            "should": [
              {
                "term": {
                  "place.keyword": "東スーパー"
                }
              },
              {
                "term": {
                  "product.keyword": "コアラのマーチ"
                }
              }
            ]
          }
        },
        {
          "range": {
            "total_price": {
              "gte": 300

            }
          }
        }
      ]
    }
  }
}
```

<details><summary>検索結果</summary><div>

```json:検索結果(例2)

{
  "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"
        }
      }
    ]
  }
}
```
</div></details>

#ElasticSearchシリーズ
- [ElasticSearchの検索クエリに関して（基礎編）] (https://qiita.com/horankey_jet_city/private/97d41c275434be897d58)
- ElaticSearchの検索クエリに関して（応用編） 
- [ElasticSearchの集約クエリに関して（基礎編）] (https://qiita.com/horankey_jet_city/private/94eda5046a8fe1febe45)
- [ElasticSearchの集約クエリに関して（応用？編）](https://qiita.com/horankey_jet_city/private/c193c7785465f73a17a0)

[^1]: デフォルトは1