SQLとElasticsearchとのクエリの比較

SQLとElasticsearchのクエリがそれぞれどう対応しているのか、よく使われると思うものについて大体まとめました。

環境

Elasticsearch 6.2.4 〜 7.5

前提条件

記載する内容は以下の条件を前提とします。

Elasticsearchで検索する際のURLの記載は省略

Elasticsearchでは検索を行う際に以下のようなURLへリクエストします。
{ElasticsearchのURL}/{index名}/_search
例えばSQLでproductというテーブルに対して索引を行っている場合、Elasticsearchでは以下のURLへリクエストを行っています。
http://localhost:9200/product/_search

以降Elasticsearchのリクエスト先URLは記載しませんが、上述のようなURLへリクエストしている前提です。

文字列のデータタイプは全てKeyword（全文検索は使わない）

Elasticsearchは全文検索が可能であり、文字列データはフィールドのデータタイプによって全文検索対象かどうかが変わります。

• 通常の文字列 : Keyword
• 全文検索対象 : Text

RDBMSでも全文検索が可能ですが利用するRDBMSによって扱い方がかなり異なるので、全文検索を行う場合はSQLと比較するよりもElasticsearch専用の構文として検索方法を理解した方が良いかなと思います。
そのため、今回は文字列のデータは全てKeywordのデータタイプである前提とします。

条件なしで検索

SQLにおけるWHERE句なしの検索は、ElasticsearchではMatch All Queryを利用します。

SQL

SELECT *
FROM product

Elasticsearch

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

Elasticsearchの検索結果はhitsというフィールドに格納されて返ってきます。

取得件数や取得開始位置を指定

SQLにおけるLIMIT(その他OFFSETだったりROWNUMだったり)を利用した取得件数や取得開始位置の指定は、ElasticsearchではFrom / Sizeを利用します。

Elasticsearchがfromとsizeで2つのパラメータを指定しているので、SQLの例はLIMITとOFFSETを指定した例を記載します。（RDBMSに合わせて適宜読み替えてください）
例えば先頭5件をスキップして10件だけ取得する場合は以下のようになります。

SQL

SELECT *
FROM product
LIMIT 10 OFFSET 5

Elasticsearch

{
  "from": 5,
  "size": 10, 
  "query": {
    "match_all": {}
  }
}

Elasticsearchのデフォルトでは"from": 0, "size": 10となっています。

取得する列を指定

SQLにおけるSELECT句での取得カラムの指定は、ElasticsearchではSource filteringの機能を利用します。

SQL

SELECT
  product_name,
  product_count 
FROM product

Elasticsearch

{
  "_source": [
    "product_name",
    "product_count"
  ],
  "query": {
    "match_all": {}
  }
}

重複行の削除

SQLにおけるSELECT句でDISTINCTキーワードを指定した重複行の削除は、ElasticsearchではField Collapsingの機能を利用します。

SQL

SELECT DISTINCT
  product_name
FROM product

Elasticsearch

{
  "collapse": {
    "field": "product_name"
  },
  "query": {
    "match_all": {}
  }
}

条件に一致するデータを検索（WHERE句）

以降の例では特定のデータタイプの場合を記載しますが、他のデータタイプを指定する場合も基本的には同様です。

指定した値に等しい

SQLにおける特定の値に等しいデータの検索は、ElasticsearchではTerm Queryを利用します。

SQL

SELECT *
FROM product
WHERE product_name = 'りんご'

Elasticsearch

{
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  }
}

指定した複数の値のいずれかに等しい（IN句）

SQLにおけるIN句を利用した検索は、ElasticsearchではTerms Queryを利用します。

SQL

SELECT *
FROM product
WHERE product_name IN ('りんご', 'にんじん')

Elasticsearch

{
  "query": {
    "terms": {
      "product_name": [
        "りんご",
        "にんじん"
      ]
    }
  }
}

指定した値が含まれている（LIKE句）

SQLにおけるLIKE句を利用した検索は、ElasticsearchではWildcard Queryを利用します。

SQL

SELECT *
FROM product
WHERE product_name LIKE '%じ%'

Elasticsearch

{
  "query": {
    "wildcard": {
      "product_name": {
        "value": "*じ*"
      }
    }
  }
}

指定した値より大きい（or 小さい）

SQLにおける指定した値に対する大小を条件にした検索は、ElasticsearchではRange Queryを利用します。
大小の条件は以下のように指定します。

SQL	Elasticsearch	MEMO
>=	gte	Greater-Than or Equal to
>	gt	Greater-Than
<=	lte	Less-Than or Equal to
<	lt	Less-Than

SQL

SELECT *
FROM product
WHERE product_count >= 10

Elasticsearch

{
  "query": {
    "range": {
      "product_count": {
        "gte" : 10
      }
    }
  }
}

複数の条件に一致

SQLにおける複数条件を指定した検索は、ElasticsearchではBool Queryのmustとshouldを利用します。
複数の条件は以下のように指定します。

SQL	Elasticsearch
AND	must
OR	should

AND条件の場合

SQL(AND)

SELECT *
FROM product
WHERE product_name = 'りんご'
  AND product_count >= 10

Elasticsearch(must)

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}

OR条件の場合

SQL

SELECT *
FROM product
WHERE product_name= 'りんご'
   OR product_count >= 10

Elasticsearch

{
  "query": {
    "bool": {
      "should": [
        {
          "term": {
            "product_name": "りんご"
          }
        },
        {
          "range": {
            "product_count": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}

ANDとORの組み合わせの場合

ANDとORを組み合わせる場合はBool Queryの中にBool Queryを書くことで実現できます。

SQL

SELECT *
FROM product
WHERE product_count >= 10
  AND (product_name = 'りんご' OR product_price = 100) 

Elasticsearch

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "count": {
              "gte": 10
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "product_name": "りんご"
                }
              },
              {
                "term": {
                  "product_price": 100
                }
              }
            ]
          }
        }
      ]
    }
  }
}

この書き方以外にもminimum_should_matchというパラメータを利用して同じ検索結果を得る方法があるのですが、SQLと比べた場合には上述の方がわかりやすいかなと思います。

指定した条件に一致しない

SQLにおける指定した条件に一致しない場合の検索は、ElasticsearchではBool Queryのmust_notを利用します。

SQL

SELECT *
FROM product
WHERE product_name <> 'りんご'

Elasticsearch

{
  "query": {
    "bool": {
      "must_not": [
        {
          "term": {
            "product_name": "りんご"
          }
        }
      ]
    }
  }
}

取得するデータを並び変える（ORDER BY句）

SQLにおけるORDER BY句を利用した並び替えは、ElasticsearchではSortの機能を利用します。

SQL

SELECT *
FROM product
ORDER BY product_name ASC

Elasticsearch

{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "product_name": {
        "order": "asc"
      }
    }
  ]
}

データを集計する

Elasticsearchでの集計にはAggregationsを利用します。
検索に比べるとElasticsearch特有の書き方がやや強いので少しわかりにくいかもしれませんが、基本的な構文は以下の通りです。

{
  "aggs": {
    "{Aggregation名(わかりやすければ何でもOK)}": {
        "{Aggregationの種類}": {}
  }
}

Elasticsearchの集計結果はhitsではなくaggregationsというフィールドに格納されて返ってきます。
検索結果は通常通りhitsの方に格納されて一緒に返ってくるので、検索結果が不要な場合はsizeを0に指定しておくと良いです。

指定した列の平均値や最大値を求める（集計関数）

SQLにおける集計関数を利用したデータ集計は、ElasticsearchではMetrics Aggregationsを利用します。
SQLの集計関数とElasticsearchの対応一覧は以下の通りです。

SQL	Elasticsearch
AVG	Avg Aggregation
MAX	Max Aggregation
MIN	Min Aggregation
SUM	Sum Aggregation

SQL

SELECT
  AVG(product_count)
FROM product

Elasticsearch

{
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}

同じ値を持つデータ毎に集計する（GROUP BY句）

SQLにおけるGROUP BY句を利用したデータ集計は、ElasticsearchではTerms Aggregationを利用します。

SQL

SELECT
  product_name
FROM product
GROUP BY product_name

Elasticsearch

{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      }
    }
  }
}

Terms Aggregationを利用した集計はsizeに指定された上位N件までが集計されます。（例では10件）

集計関数と組み合わせたい場合はAggregationをネストさせます。

SQL

SELECT
  AVG(product_count)
FROM product
GROUP BY product_name

Elasticsearch

{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_avg": {
          "avg": {
            "field": "product_count"
          }
        }
      }
    }
  }
}

指定した条件のデータを集計する（WHERE句との組み合わせ）

元となるデータを絞り込んだ上で集計したい場合、queryと組み合わせることで実現できます。

SQL

SELECT
  AVG(product_count)
FROM product
WHERE product_name = 'りんご'

Elasticsearch

{
  "size": 0,
  "query": {
    "term": {
      "product_name": {
        "value": "りんご"
      }
    }
  },
  "aggs": {
    "product_count_avg": {
      "avg": {
        "field": "product_count"
      }
    }
  }
}

ちょっとわかりにくいのですが、queryで絞り込んだ結果が返却されるhitsと、集計結果が返却されるaggregationsはそれぞれ別の項目です。
集計結果のみが取得できれば良い場合、上述の例のようにsizeに0を指定することでhitsに何も返却されなくなるため、レスポンスデータが軽くなります。
（aggregationsにはちゃんとqueryで指定した条件に一致するデータから集計した結果が返却されます）

集計結果に対して条件を指定する（HAVING句）

SQLにおけるHAVING句を利用した集計結果に対する検索は、ElasticsearchではBucket Selector Aggregationを利用します。

SQL

SELECT
  product_name,
  SUM(product_count)
FROM product
GROUP BY product_name
HAVING SUM(product_count) > 10

Elasticsearch

{
  "aggs": {
    "product_name_aggs": {
      "terms": {
        "field": "product_name",
        "size": 10
      },
      "aggs": {
        "product_count_sum": {
          "sum": {
            "field": "product_count"
          }
        },
        "product_count_sum_bucket": {
          "bucket_selector": {
            "buckets_path": {
              "count_sum": "product_count_sum"
            },
            "script": "params.count_sum > 10"
          }
        }
      }
    }
  }
}

buckets_pathに絞り込み条件の対象となる項目を指定します。
例ではSUM(product_count)に相当するproduct_count_sumを条件指定の対象にしています。

scriptには絞り込み条件を記述します。
buckets_pathで指定した値を利用したい場合はparams.count_sumというように変数名を指定することで実現できます。
paramsは実行したクエリ内の変数を参照する場合に指定する値です。

その他JOINとかサブクエリとか

ElasticsearchはRDBMSでは無いため、残念ながらJOINやサブクエリのような機能はありません。
登録するデータを非正規化したり、データをネストして持つことが出来るNested datatypeを利用したりして上手く検索できるようにデータを登録する必要があります。