ES中如何查询Top10数据

问题描述：

在实际项目中，查询Top10数据的场景非常常见，比如查询票房前十的电影，销售榜前十的商品等。
那么在ES中如何查询Top10的数据呢？

问题分析：

一般Top10问题，都可以转化成先排序再取排行前10的问题，那么实现就简单了。

数据准备

创建索引book，获取价格最贵的3本书的信息。

PUT book
{
  "mappings": {
    "properties": {
      "name": {
        "type": "text", "analyzer": "ik_smart"
      },
      "price": {
        "type": "double"
      }
    }
  }
}

PUT /book/_bulk
{ "create": { } }
{"name": "java编程思想","price": 100}
{ "create": { } }
{"name": "ES实战","price": 120}
{ "create": { } }
{"name": "ES从入门到精通","price": 60}
{ "create": { } }
{"name": "微服务架构 设计模式","price": 160}
{ "create": { } }
{"name": "架构真经","price": 90}
{ "create": { } }
{"name": "spring boot实战","price": 50}
{ "create": { } }
{"name": "高性能mysql","price": 80}

实现方案：

1、SQL查询

POST /_sql?format=txt
{
  "query": "SELECT * FROM book ORDER BY price DESC",
  "fetch_size": 3
}

POST /_sql?format=txt
{
  "query": "SELECT * FROM (SELECT * FROM book ORDER BY price DESC ) limit 3"
}

查询结果：

2、DSL查询

POST /_sql/translate
{
  "query": "SELECT * FROM book ORDER BY price DESC",
  "fetch_size": 3
}

转化结果：

{
  "size" : 3,
  "_source" : {
    "includes" : [
      "name",
      "price"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "price" : {
        "order" : "desc",
        "missing" : "_first",
        "unmapped_type" : "double"
      }
    }
  ]
}

完整DSL语句：

POST /book/_search
{
  "size" : 3,
  "_source" : {
    "includes" : [
      "name",
      "price"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "price" : {
        "order" : "desc",
        "missing" : "_first",
        "unmapped_type" : "double"
      }
    }
  ]
}