ElasticSearch DSL 简单梳理

声明：
本文为方便日后部分DSL结构速查，由于索引设计不同，语法会有差异,本文默认String 保存为keyword类型，详细了解移步Elastic Stack 官网

https://www.elastic.co/guide/cn/elasticsearch/guide/current/index.html
https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html

习惯用SQL的同学，也可以直接用es的translate接口转换为DSL作为参考,如下：

GET /_sql/translate
{
  "query":"select OrderId,etl_route_name from erp_order where etl_deleted = 0 "
}

1.EXP (=)

SELECT * FROM ERP_ORDER WHERE ETL_DELETE = 0

1.DSL

GET /erp_order/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_deleted": 0
          }
        }  
      ]
    }
  }
}

2.EXP(!=)

SELECT * FROM ERP_ORDER WHERE ETL_DELETE = 0 AND ETL_DELETE != 1

2.DSL

GET /erp_order/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_deleted": 0
          }
        }  
      ],
       "must_not":
        {
          "term": {
            "etl_deleted": 1
          }
        }
    }
  }
}

3.EXP(in)

SELECT * FROM ERP_ORDER WHERE ETL_ORDER_TYPE  IN (1,2,3,4,5)

3.DSL

GET /erp_order/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "terms": {
            "etl_order_type": [ 1, 2,3, 4,5 ]
          }
        }
      ]
    }
  }
}

4.EXP(>,<,>=,<=)

SELECT  * FROM ERP_ORDER WHERE etl_create_time_date  >= "2010-04-01T00:00:00+08:00" AND etl_create_time_date < "2021-05-01T00:00:00+08:00"

4.DSL

GET /erp_order/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "etl_create_time_date": {
              "gte": "2010-04-01T00:00:00+08:00",
              "lt": "2021-05-01T00:00:00+08:00"
            }
          }
        }
      ]
    }
  }
}

5.EXP(limit)

SELECT * FROM ERP_ORDER WHERE ETL_DELETE = 0 limit 10,20

5.DSL

GET /erp_order/_search
{ "from": 10,
"size": 20,
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_deleted": 0
          }
        }  
      ]
    }
  }
}

6.EXP(count(1))

SELECT COUNT(1) FROM ERP_ORDER WHERE ETL_IS_TEST = false AND ETL_DELETED = 0

6.DSL

GET /order_oms_order/_search?size=0&filter_path=hits
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_is_test": false
          }
        },
        {
          "term": {
            "etl_deleted": 0
          }
        }
      ]
    }
  }
}

7.EXP(COUNT-DISTINCT)

SELECT COUNT(DISTINCT ORDERID) FROM erp_order where etl_deleted = 0 

7.DSL

GET /erp_order/_search?size=0&filter_path=aggregations
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_deleted": 0
          }
        }
      ]
    }
  },
  "aggs": {
    "count": {
      "cardinality": {
        "field": "OrderId"
      }
    }
  }
}

8.EXP(COUNT-GROUP)

SELECT COUNT(ORDERID) FROM erp_order where etl_deleted = 0 group by etl_order_type 

8.DSL

GET /erp_order/_search?size=0&filter_path=aggregations
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_deleted": 0
          }
        }
      ]
    }
  },
  "aggs": {
    "type": {
      "terms": {
        "field": "etl_order_type"
      }
    }
  }
}

9.EXP(SUM-GROUP)

SELECT SUM(TotalPrice),etl_order_type FROM erp_order where etl_deleted = 0 group by etl_order_type 

9.DSL

GET /erp_order/_search?size=0&filter_path=aggregations
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "etl_deleted": 0
          }
        }
      ]
    }
  },
  "aggs": {
    "typeNode": {
      "terms": {
        "field": "etl_order_type"
      },
      "aggs": {
        "totalPrice": {
          "sum": {
            "field": "TotalPrice",
            "format": "0.00"
          }
        }
      }
    }
  }
}

10.EXP(SUM-GROUP-ORDER-LIMIT)

SELECT SUM(TotalPrice) AS totalAmonut, SUM(OrderTotalCount) etl_route_name as totalCount  FROM erp_order where etl_is_test = false group by etl_order_type order by totalCount desc limit 10

10.DSL

GET /erp_order/_search?size=0&filter_path=aggregations
{
  "query": {
    "bool": {
     
      "filter": [
        {
          "term": {
            "etl_is_test": false
          }
        }
      ]
    }
  },
  "aggs": {
    "province": {
      "terms": {
        "field": "etl_route_name"
         ,"size": 10
      },
      "aggs": {
        "totalCount": {
          "sum": {
            "field": "OrderTotalCount",
            "format": "0"
          }
        },
        "totalAmonut": {
          "sum": {
            "field": "TotalPrice",
            "format": "0.00"
          }
        },
        "province_bucket_sort": {
          "bucket_sort": {
            "sort": {
              "totalCount": {
                "order": "desc"
              }
            }
          }
        }
      }
    }
  }
}

11.EXP(GROUP BY TIME)

SELECT count(1) ,DATE_FORMAT(etl_create_date,“yyyy-MM-dd”) as key_as_string  group by key_as_string  

11.1.DSL

GET /erp_order/_search?size=0&filter_path=aggregations
{
  "aggs": {
    "timeNodes": {
      "date_histogram": {
        "field": "etl_create_date",
        "calendar_interval": "day",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

11.2.DSL(填充时间段内缺失的时间数据)

GET /erp_order/_search?size=0&filter_path=aggregations
{
    "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "etl_create_time_date": {
              "gte": "2010-04-01T00:00:00+08:00",
              "lte": "2021-05-01T00:00:00+08:00"
            }
          }
        }
      ]
    }
  },

   "aggs": {
    "timeNodes": {
      "date_histogram": {
        "field": "etl_create_date",
        "calendar_interval": "day",
        "format": "yyyy-MM-dd",
         "min_doc_count" : 0,
         "extended_bounds" : {
                "min" : "2021-04-01",
                "max" : "2021-05-01"
         }
      }
    }
  }
}