【Snippet】Elastic Search の検索クエリ

7 min read読了の目安(約6600字

◆ 初めに

検索対象のデータは、下記のページを参考に作成した、index: zipcodesに対して検索する

https://qiita.com/satto_sann/items/0ccb1f28af8b3f103949

queryは、下記のqiitaを参考にしました。

https://qiita.com/NAO_MK2/items/630f2c4caa0e8a42407c#指定した値が含まれているlike句
https://qiita.com/kieaiaarh/items/5ea4e8a188bd9814000d#rest-request-body

◆ APIを叩く時の、query以外の部分は、下記を参照

console
curl -XGET -H 'Content-Type: application/json' 'http://elasticsearch:9200/zipcodes/_search?pretty' -d '
{
  検索クエリをここに書く
}'
kibana-DevTools
GET /zipcodes/_search
{
  検索クエリをここに書く
}

node.js の request module を使用する場合
const request = require('request')

const body = {
  検索クエリをここに書く
}

const options = {
    url: 'http://elasticsearch:9200/zipcodes/_search?pretty',
    method: 'GET',
    json: true,
    body: body
    
}
request(options, function(error, response, body) {
    console.log(body.hits);
});

◆ 全行取得

SELECT * FROM zipcodes

kibana-DevTools
GET zipcodes/_search
{
  "size": 100, 
  "_source": [], 
  "sort": [],
  "query": {
    "match_all": {}
  }
}

◆ 列選択 / 並び替え

SELECT

sql
SELECT city from zipcodes
kibana-DevTools
{
  "size": 100, 
  "_source": [ "prefecture", "city" ], 
  "sort": [],

  "query": {
    "match_all": {}
  }
}

ORDER BY

sql
ORDER BY prefectureKana ASC
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [
    { "prefectureKana": { "order": "asc" } }
  ], 

  "query": {
    "match_all": {}
  }
}

◆ WHERE(単一条件/ = != > <)

=

sql
SELECT *
FROM zipcodes
WHERE prefecture = '東京都'
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "term": { "prefecture": "東京都" }
  }
}

!=

sql
SELECT *
FROM zipcodes
WHERE prefecture != "北海道"
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "bool": {
      "must_not": [
        { "term": { "prefecture": "北海道" } }
      ]
    }
  }
}

<=

sql
SELECT *
FROM zipcodes
WHERE JIS <= 1103
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "range": { "jis": { "lte": 1103 } }
  }
}

<= + >=

sql
SELECT *
FROM zipcodes
WHERE 1102 <= JIS <= 1103"
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "range": { 
      "jis": {
        "gte": 1102, 
        "lte": 1103 
      }
    }
  }
}

◆ WHERE(単一条件/ IN LIKE)

IN

sql
SELECT *
FROM zipcodes
WHERE prefecture IN ( '東京都', '神奈川区' )
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "terms": { "prefecture": [ "東京都", "神奈川県" ] }
  }
}

LIKE

sql
SELECT *
FROM zipcodes
WHERE city LIKE '西多摩郡&'
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "wildcard": { "city": "西多摩郡*" }
  }
}

◆ WHERE(複数条件)

AND

sql
SELECT *
FROM zipcodes
WHERE prefecture = '千葉県' AND updated = 1
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "bool": {
      "must": [
        { "term": { "prefecture": "千葉県" } },
        { "term": { "updated": "1" } }
      ]
    }
  }
}

AND (= と !=)

sql
SELECT *
FROM zipcodes
WHERE updated = 1 OR updateReason != 5
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "bool": {
      "must": [ { "term": { "updated": 1 } } ],
      "must_not": [ { "term": { "updateReason": 5 } } ]
    }
  }
}

OR

sql
SELECT *
FROM zipcodes
WHERE updated = 1 OR city = '文京区'
kibana-DevTools
{
  "size": 0, 
  "_source": [], 
  "sort": [],
  
  "query": {
    "bool": {
      "should": [
        { "term": { "city": { "value": "文京区" } } },
        { "term": { "updated": { "value": 1 } } }
      ]
    }
  }
  
}

AND + OR

sql
SELECT *
FROM zipcodes
WHERE updated = 1 AND ( updateReason = 3 OR updateReason = 5 )
kibana-DevTools
{
  "size": 100, 
  "_source": [], 
  "sort": [],

  "query": {
    "bool": {
      "must": [
        { 
          "term": { "updated": 1 }
        },
        { 
          "bool": { 
            "should": [
              { "term": { "updateReason": 1 } },
              { "term": { "updateReason": 3 } }
            ] 
          }
        } 
      ]
    }
  }
}

◆ GROUP BY / COUNT / SUM / etc...

COUNT + GROUP BY

sql
SELECT prefecture, count(*) 
FROM zipcodes 
GROUP BY prefecture
kibana-DevTools
{
  "size": 0, 
  "_source": [], 
  "sort": [],
  
  "query": { 
    "match_all": {} 
  },
  
  "aggs": {
    "県ごとのレコード数をカウント": {
      "terms": {
        "field": "prefecture",
        "size": 100
      }
    }
  }
}

SUM + WHERE

sql
SELECT SUM(updated)
FROM zipcodes
WHERE prefecture = '千葉県'
kibana-DevTools
{
  "size": 0, 
  "_source": [], 
  "sort": [],
  
  "query": { 
    "term": { "prefecture": "千葉県" }
  },
  
  "aggs": {
    "updatedフィールドの値を合計する": {
      "sum": {
        "field": "updated"
      }
    }
  }
  
}

SUM + GROUP BY

sql
SELECT prefecture, SUM(updated) 
FROM zipcodes 
GROUP BY prefecture
kibana-DevTools
{
  "size": 0, 
  "_source": [], 
  "sort": [],
  
  "query": { 
    "match_all": {}
  },
  
  "aggs": {
    "県ごとに集約": {
      "terms": { "field": "prefecture", "size": 100 },
      "aggs": { "updatedの合計": { "sum": { "field": "updated" } }
      }
    }
  }

}

SUM + GROUP BY + HAVING

sql
SELECT prefecture, SUM(updated)
FROM zipcodes 
GROUP BY prefecture 
HAVING SUM (updated >= 1)
kibana-DevTools
{
  "size": 0, 
  "_source": [], 
  "sort": [],
  
  "query": { 
    "match_all": {}
  },
  
  "aggs": {
    "県ごとに集約": {
      "terms": { "field": "prefecture", "size": 100 },
      "aggs": { 
        "updatedの合計": { "sum": { "field": "updated" } },
        "HAVING用バケット": {
          "bucket_selector": {
            "buckets_path": {"updatedSum": "updatedの合計"},
            "script": "params.updatedSum >= 1" 
          }
        }
      }
    }
  }
}

県毎に纒める ⇨ updatedが立ってるやつだけ ⇨ postcodeだけ表示

kibana-DevTools
{
  "size": 0, 
  "_source": [], 
  "sort": [],
  
  "query": { 
    "match_all": {}
  },
  
  "aggs": {
    "県毎に集約": {
      "terms": { "field": "prefecture", "size": 100 },
      "aggs": {
        "updatedフラグが立っているものだけ": {
          "filter": { "range": { "updated": { "gte": 1 } } }, 
          "aggs": {
            "ヒット": {
              "top_hits": { "_source": "postcode", "size": 100 }
            }
          }
        }
      }
    }
  }