LoginSignup
5
6

More than 3 years have passed since last update.

@yurucamp(深津大好きおじさん)

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

Last updated at Posted at 2019-05-31

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クエリ
"filter": [
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  { 
     {{基本的な検索クエリ　または　boolクエリ}} 
  },
  ...
]

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

  • productが「杉のこ村」、「キノコの山」、
  • placeが「東スーパー」
  • @timestampが2019-06-28以上2019-06-29未満
リクエスト(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"
            }
          }
        }
      ]
    }
  }
}

検索結果
検索結果(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"
        }
      }
    ]
  }
}

mustクエリ

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

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

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

  • productが「杉のこ村」、「キノコの山」、
  • placeが「東スーパー」
  • @timestampが2019-06-28以上2019-06-29未満
リクエスト(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"
            }
          }
        }
      ]
    }
  }
}

検索結果
検索結果(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"
        }
      }
    ]
  }
}

OR条件

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

shouldクエリ

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

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

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

  • productが「杉のこ村」にマッチする
  • total_priceが300以上10000以下
  • placeが「南商店」にマッチする
リクエスト(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
    }
  }

}

検索結果
検索結果(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"
        }
      }
    ]
  }
}

NOT条件

指定した検索条件の否定を満たすdocumentを検索。

must_notクエリ

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

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

  • kind_nameが「アクセス」
  • productが「杉のこ村」
  • placeが「北デパート」
  • placeが「西コンビニ」
  • placeが「南商店」
リクエスト(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": "南商店"
          }
        }
      ]
    }
  }
}

検索結果
検索結果(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"
        }
      }
    ]
  }
}

さらに複雑な検索クエリ

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

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

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

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

  • user_idが「39」かつkind_nameが「アクセス」かつplaceが「東スーパー」
  • user_idが「59」かつkind_nameが「購入」かつplaceが「南商店」かつproductが「コアラのマーチ」
リクエスト(例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": "コアラのマーチ"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

検索結果
検索結果(例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"
        }
      }
    ]
  }
}

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

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

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

            }
          }
        }
      ]
    }
  }
}

検索結果
検索結果(例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"
        }
      }
    ]
  }
}

ElasticSearchシリーズ

  1. デフォルトは1 ↩

5
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
6