ElasticSearch笔记系列(8)——简单查询、条件查询、聚合查询

Stella981
• 阅读 435

ES的查询方式可以分为三类:

简单查询

条件查询

聚合查询

数据准备

我们先按照前几篇介绍的插入文档的方法准备一些实验数据,然后再来演示如何实现各类查询。

我们在我们之前创建的rent索引的community类型下面加入如下文档信息。

回忆一下:当时创建的索引及类型如下:

{
    "settings":{
        "number_of_shards":3,
        "number_of_replicas":1
    },
    "mappings":{
        "community":{
            "properties":{
                "communityname":{
                    "type":"text"
                },
                "city":{
                    "type":"keyword"
                },
                "age":{
                    "type":"integer"
                },
                "creationdate":{
                    "type":"date",
                    "format":"yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
                }
            }
        },
        "shop":{
            
        }
    }
}

回过头来,我们现在插入一些文档进去。插入的ES的API:http://localhost:9200/rent/community/

ElasticSearch笔记系列(8)——简单查询、条件查询、聚合查询

所有文档的插入请求体如下:你可以把这些数据插入到你的ES中,然后try后续的各种查询。

{
    "communityname":"万科阳光苑",
    "city":"上海",
    "age":10,
    "creationdate":"2008-01-01 00:00:00"
}

{
    "communityname":"万科朗润园",
    "city":"上海",
    "age":12,
    "creationdate":"2006-01-01 00:00:00"
}

{
    "communityname":"万科优诗美地",
    "city":"上海闵行七宝",
    "age":19,
    "creationdate":"1999-01-01 00:00:00"
}

{
    "communityname":"万科清林径",
    "city":"上海浦东新区新场镇",
    "age":6,
    "creationdate":"2012-01-01 00:00:00"
}

{
    "communityname":"万科蓝山",
    "city":"上海浦东曹路",
    "age":14,
    "creationdate":"2004-01-01 00:00:00"
}

{
    "communityname":"万科公园大道",
    "city":"上海老闵行",
    "age":3,
    "creationdate":"2015-01-01 00:00:00"
}

{
    "communityname":"万科白马花园",
    "city":"上海莘闵别墅区",
    "age":15,
    "creationdate":"2003-01-01 00:00:00"
}

{
    "communityname":"金地艺境(宝山)",
    "city":"上海宝山区",
    "age":6,
    "creationdate":"2012-01-01 00:00:00"
}

{
    "communityname":"金地艺境(松江)",
    "city":"上海松江区",
    "age":3,
    "creationdate":"2015-01-01 00:00:00"
}

{
    "communityname":"金地艺华年",
    "city":"上海浦东航头",
    "age":5,
    "creationdate":"2013-01-01"
}

{
    "communityname":"金地格林世界",
    "city":"上海嘉定南翔",
    "age":7,
    "creationdate":"2011-01-01"
}

{
    "communityname":"保利西子湾",
    "city":"上海松江大学城",
    "age":10,
    "creationdate":"2008-01-01"
}

{
    "communityname":"保利艾庐",
    "city":"上海浦东新区周浦镇",
    "age":3,
    "creationdate":"2015-01-01"
}

{
    "communityname":"中海悦府",
    "city":"上海松江泗泾",
    "age":4,
    "creationdate":"2014-01-01"
}

{
    "communityname":"中海万锦城(二期)",
    "city":"上海闸北不夜城",
    "age":6,
    "creationdate":"2012-01-01"
}

{
    "communityname":"中海紫御豪庭",
    "city":"上海普陀长征",
    "age":6,
    "creationdate":"2012-01-01"
}

{
    "communityname":"象屿鼎城",
    "city":"上海浦东川沙",
    "age":5,
    "creationdate":"2013-01-01"
}

插入结果如下:我们通过head插件页面的数据查询查看

ElasticSearch笔记系列(8)——简单查询、条件查询、聚合查询

(本文为oschina博主happybks的博文:https://my.oschina.net/happyBKs/blog/1798778)

简单查询

简单查询其实我们已经演示过。就是按照文档_id,通过ES的RESTFUL API 的GET请求来进行文档查询。

例如,我这里插入了租赁房屋rent索引下的小区community类型中的文档万科阳光苑的文档_id为AWLY7wnkoILHeA4gRvV8

那么我们请求get请求下面的url来进行文档简单查询

http://localhost:9200/rent/community/AWLY7wnkoILHeA4gRvV8

ElasticSearch笔记系列(8)——简单查询、条件查询、聚合查询

条件查询

与简单查询不同的是:

条件查询请求的协议方法是POST方法。

条件查询需要使用_search关键字。

条件查询需要在请求体内将条件写在一个json体内。

查询所有

在查询的请求体内的查询条件json中,都是用query关键词的。(请求url中的用的额是_search关键字)

查询符合条件的所有数据,需要用match_all

这里我们查询所有的,即不设置筛选条件,那么就用空的花括号{}

POST请求http://localhost:9200/rent/community/\_search

请求体raw,类型JSON

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

查询结果:total刚好18个,就是我们刚才插入的18个全部文档。

我们看一下返回结果的几个关键属性。took代表查询所耗费的毫秒数。hits代表响应的全部结果。

hits中的hits返回的是10条结果。是的,虽然我们查询到18条,但是这里默认只会列出10条。

{
    "took": 125,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 1,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 1,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFA5EoILHeA4gRvWF",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(松江)",
                    "city": "上海松江区",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 1,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZF4FAoILHeA4gRvWI",
                "_score": 1,
                "_source": {
                    "communityname": "保利西子湾",
                    "city": "上海松江大学城",
                    "age": 10,
                    "creationdate": "2008-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFmeqoILHeA4gRvWH",
                "_score": 1,
                "_source": {
                    "communityname": "金地格林世界",
                    "city": "上海嘉定南翔",
                    "age": 7,
                    "creationdate": "2011-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEy0foILHeA4gRvWE",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(宝山)",
                    "city": "上海宝山区",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            }
        ]
    }
}

那么我们怎设定从哪里返回、返回多少条呢?

我们需要修改一下查询条件:

比如我们希望吧所有18条结果都返回显示在hits中。

条件需写成:

{
    "query":{
        "match_all":{}
    },
    "from":0,
    "size":18
}

查询结果:

{
    "took": 9,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 1,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 1,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFA5EoILHeA4gRvWF",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(松江)",
                    "city": "上海松江区",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 1,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZF4FAoILHeA4gRvWI",
                "_score": 1,
                "_source": {
                    "communityname": "保利西子湾",
                    "city": "上海松江大学城",
                    "age": 10,
                    "creationdate": "2008-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFmeqoILHeA4gRvWH",
                "_score": 1,
                "_source": {
                    "communityname": "金地格林世界",
                    "city": "上海嘉定南翔",
                    "age": 7,
                    "creationdate": "2011-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEy0foILHeA4gRvWE",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(宝山)",
                    "city": "上海宝山区",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZHsN6oILHeA4gRvWO",
                "_score": 1,
                "_source": {
                    "communityname": "象屿鼎城",
                    "city": "上海浦东川沙",
                    "age": 5,
                    "creationdate": "2013-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGbCzoILHeA4gRvWL",
                "_score": 1,
                "_source": {
                    "communityname": "中海悦府",
                    "city": "上海松江泗泾",
                    "age": 4,
                    "creationdate": "2014-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLDhHMpoILHeA4gRvV7",
                "_score": 1,
                "_source": {
                    "communityname": "世茂滨江花园",
                    "city": "上海",
                    "age": 9,
                    "creationdate": "2009-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZCaoSoILHeA4gRvV-",
                "_score": 1,
                "_source": {
                    "communityname": "万科朗润园",
                    "city": "上海",
                    "age": 12,
                    "creationdate": "2006-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDcK7oILHeA4gRvWB",
                "_score": 1,
                "_source": {
                    "communityname": "万科蓝山",
                    "city": "上海浦东曹路",
                    "age": 14,
                    "creationdate": "2004-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFV2doILHeA4gRvWG",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺华年",
                    "city": "上海浦东航头",
                    "age": 5,
                    "creationdate": "2013-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGGD2oILHeA4gRvWJ",
                "_score": 1,
                "_source": {
                    "communityname": "保利艾庐",
                    "city": "上海浦东新区周浦镇",
                    "age": 3,
                    "creationdate": "2015-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZHR2loILHeA4gRvWN",
                "_score": 1,
                "_source": {
                    "communityname": "中海紫御豪庭",
                    "city": "上海普陀长征",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            }
        ]
    }
}

当然我们如果只需要从特定位置开始,特定若干条,可以通过from和size两个条件属性关键词中设定。

注意:from是从0开始的,例如我要查询第一条开始的1条数据:

{
    "query":{
        "match_all":{}
    },
    "from":0,
    "size":1
}

结果:

{
    "took": 2,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            }
        ]
    }
}

如何进行关键词查询?

文本类型属性条件匹配

需要将关键词match_all改成match。

POST http://localhost:9200/rent/community/\_search

{
    "query":{
        "match":{
            "communityname":"万科"
        }
    }
}

我们查询小区名字为万科有关的所有小区文档:

结果发现,我们的确查找到了所有万科有关的小区的文档数据。但是有一个“中海万锦城”居然也被查询到了,这是因为有个“万”字。这是因为两点:

一个是我们条件中的communityname是一个text类型,是按照一般文本进行匹配查询的;

另一个原因是使用match关键字进行查询,不等同于sql里的条件查询,而是一个个字或者说分词的匹配查询,在汉字里,我们默认一个字为一个匹配的分词,所以查询到的结果包含了单字匹配的结果。

好上面这个我们先放一下,看一下匹配到额结果:

hits中返回的结果列表的排序顺序默认是按照score的倒排顺序排列的。

{
    "took": 191,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 8,
        "max_score": 2.5742233,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDcK7oILHeA4gRvWB",
                "_score": 2.5742233,
                "_source": {
                    "communityname": "万科蓝山",
                    "city": "上海浦东曹路",
                    "age": 14,
                    "creationdate": "2004-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZCaoSoILHeA4gRvV-",
                "_score": 2.0816076,
                "_source": {
                    "communityname": "万科朗润园",
                    "city": "上海",
                    "age": 12,
                    "creationdate": "2006-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1.82076,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1.82076,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 0.7981695,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 0.72827405,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 0.72827405,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 0.63701355,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            }
        ]
    }
}

关键字类型属性条件匹配

我们再按照city地点属性来进行查询:
POST http://localhost:9200/rent/community/\_search

请求体条件:

{
    "query":{
        "match":{
            "city":"浦东新区"
        }
    }
}

返回的结果是什么都没有:

原因是我们索引结构中,city属性是一个keyword类型,需要把整个按照关键字进行匹配,而不是按照文本来进行检索查询。

{
    "took": 131,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 0,
        "max_score": null,
        "hits": []
    }
}

keyword类型的属性作为match查询条件时,需要完全匹配对应上才可以。

如,我们请求:

{
    "query":{
        "match":{
            "city":"上海浦东新区周浦镇"
        }
    }
}

返回结果:

{
    "took": 29,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 1,
        "max_score": 1.2039728,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGGD2oILHeA4gRvWJ",
                "_score": 1.2039728,
                "_source": {
                    "communityname": "保利艾庐",
                    "city": "上海浦东新区周浦镇",
                    "age": 3,
                    "creationdate": "2015-01-01"
                }
            }
        ]
    }
}

排序

前面我们说了,那个查询小区名字匹配“万科”的结果是按照score的倒排顺序排列的。如果我们希望按照我们特定的属性字段来排序,需要用sort关键字来指定。

POST http://localhost:9200/rent/community/\_search

请求体条件:

{
    "query":{
        "match":{
            "communityname":"万科"
        }
    },
    "sort":[
        {"creationdate":{"order":"desc"}}
    ]
}

查询结果:

{
    "took": 322,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 8,
        "max_score": null,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": null,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                },
                "sort": [
                    1420070400000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": null,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                },
                "sort": [
                    1325376000000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": null,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                },
                "sort": [
                    1325376000000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": null,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                },
                "sort": [
                    1199145600000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZCaoSoILHeA4gRvV-",
                "_score": null,
                "_source": {
                    "communityname": "万科朗润园",
                    "city": "上海",
                    "age": 12,
                    "creationdate": "2006-01-01 00:00:00"
                },
                "sort": [
                    1136073600000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDcK7oILHeA4gRvWB",
                "_score": null,
                "_source": {
                    "communityname": "万科蓝山",
                    "city": "上海浦东曹路",
                    "age": 14,
                    "creationdate": "2004-01-01 00:00:00"
                },
                "sort": [
                    1072915200000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": null,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                },
                "sort": [
                    1041379200000
                ]
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": null,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                },
                "sort": [
                    915148800000
                ]
            }
        ]
    }
}

聚合查询

aggs是聚合查询的关键词。这里我想把所有的小区按照它们的房龄进行聚合。

这里给我们的聚合条件取一个名字my_group_by_age。这个名字是自定义的,你可以随便起。

然后我们使用terms关键词,指明我们需要按照某个字段进行聚合。指定字段需要用到filed关键词。

然后查询:

POST http://localhost:9200/rent/community/\_search

{
    "aggs":{
        "my_group_by_age":{
            "terms":{
                "field":"age"
            }
        }
    }
}

查询结果:

我们可以看到结果就送中返回了涉及的文档个数18个,以及默认显示10个文档。另外将聚合的结果也给出了。key代表我们请求条件中的terms的field关键词对应设置的字段。我们可以看到不同age房龄的小区文档个数。

{
    "took": 568,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 1,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 1,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFA5EoILHeA4gRvWF",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(松江)",
                    "city": "上海松江区",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 1,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZF4FAoILHeA4gRvWI",
                "_score": 1,
                "_source": {
                    "communityname": "保利西子湾",
                    "city": "上海松江大学城",
                    "age": 10,
                    "creationdate": "2008-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFmeqoILHeA4gRvWH",
                "_score": 1,
                "_source": {
                    "communityname": "金地格林世界",
                    "city": "上海嘉定南翔",
                    "age": 7,
                    "creationdate": "2011-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEy0foILHeA4gRvWE",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(宝山)",
                    "city": "上海宝山区",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            }
        ]
    },
    "aggregations": {
        "my_group_by_age": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 1,
            "buckets": [
                {
                    "key": 6,
                    "doc_count": 4
                },
                {
                    "key": 3,
                    "doc_count": 3
                },
                {
                    "key": 5,
                    "doc_count": 2
                },
                {
                    "key": 10,
                    "doc_count": 2
                },
                {
                    "key": 4,
                    "doc_count": 1
                },
                {
                    "key": 7,
                    "doc_count": 1
                },
                {
                    "key": 9,
                    "doc_count": 1
                },
                {
                    "key": 12,
                    "doc_count": 1
                },
                {
                    "key": 14,
                    "doc_count": 1
                },
                {
                    "key": 15,
                    "doc_count": 1
                }
            ]
        }
    }
}

多个分组聚合

以上是单个分组聚合,还可以进行多个分组聚合。

我们尝试追加一个聚合条件,按照建造时间来进行聚合:

{
    "aggs":{
        "my_group_by_age":{
            "terms":{
                "field":"age"
            }
        },
        "my_group_by_creationdate":{
            "terms":{
                "field":"creationdate"
            }
        }
    }
}

结果:

这里值得注意的是,建造时间creationdate是一个date类型,无论你在录入这条文档时用的是什么格式,这里聚合是都是以这个date对应的时间戳数值作为聚合的key,不过结果里会在附加一个key_as_string把date对应的日期格式打印出来。我们发现之前那几个没时分秒的日期也被转换成了时间戳,key_as_string显示为年月日时分秒的格式。

{
    "took": 530,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 1,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 1,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFA5EoILHeA4gRvWF",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(松江)",
                    "city": "上海松江区",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 1,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZF4FAoILHeA4gRvWI",
                "_score": 1,
                "_source": {
                    "communityname": "保利西子湾",
                    "city": "上海松江大学城",
                    "age": 10,
                    "creationdate": "2008-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFmeqoILHeA4gRvWH",
                "_score": 1,
                "_source": {
                    "communityname": "金地格林世界",
                    "city": "上海嘉定南翔",
                    "age": 7,
                    "creationdate": "2011-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEy0foILHeA4gRvWE",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(宝山)",
                    "city": "上海宝山区",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            }
        ]
    },
    "aggregations": {
        "my_group_by_creationdate": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 1,
            "buckets": [
                {
                    "key": 1325376000000,
                    "key_as_string": "2012-01-01 00:00:00",
                    "doc_count": 4
                },
                {
                    "key": 1420070400000,
                    "key_as_string": "2015-01-01 00:00:00",
                    "doc_count": 3
                },
                {
                    "key": 1199145600000,
                    "key_as_string": "2008-01-01 00:00:00",
                    "doc_count": 2
                },
                {
                    "key": 1356998400000,
                    "key_as_string": "2013-01-01 00:00:00",
                    "doc_count": 2
                },
                {
                    "key": 915148800000,
                    "key_as_string": "1999-01-01 00:00:00",
                    "doc_count": 1
                },
                {
                    "key": 1041379200000,
                    "key_as_string": "2003-01-01 00:00:00",
                    "doc_count": 1
                },
                {
                    "key": 1072915200000,
                    "key_as_string": "2004-01-01 00:00:00",
                    "doc_count": 1
                },
                {
                    "key": 1136073600000,
                    "key_as_string": "2006-01-01 00:00:00",
                    "doc_count": 1
                },
                {
                    "key": 1230768000000,
                    "key_as_string": "2009-01-01 00:00:00",
                    "doc_count": 1
                },
                {
                    "key": 1293840000000,
                    "key_as_string": "2011-01-01 00:00:00",
                    "doc_count": 1
                }
            ]
        },
        "my_group_by_age": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 1,
            "buckets": [
                {
                    "key": 6,
                    "doc_count": 4
                },
                {
                    "key": 3,
                    "doc_count": 3
                },
                {
                    "key": 5,
                    "doc_count": 2
                },
                {
                    "key": 10,
                    "doc_count": 2
                },
                {
                    "key": 4,
                    "doc_count": 1
                },
                {
                    "key": 7,
                    "doc_count": 1
                },
                {
                    "key": 9,
                    "doc_count": 1
                },
                {
                    "key": 12,
                    "doc_count": 1
                },
                {
                    "key": 14,
                    "doc_count": 1
                },
                {
                    "key": 15,
                    "doc_count": 1
                }
            ]
        }
    }
}

其他聚合操作

stats 各类聚合统计计算各类聚合信息。包含count、max、min、avg等。

话不多说,我们来对房龄age进行聚合统计。

请求:

POST http://localhost:9200/rent/community/\_search

{
    "aggs":{
        "my_stats_age":{
            "stats":{
                "field":"age"
            }
        }
    }
}

查询结果:我们可以看到房龄统计了总共count=18个文档,其中最大max=19,最小房龄min=3,平均房龄avg=7.944444444444445

{
    "took": 133,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 1,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 1,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFA5EoILHeA4gRvWF",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(松江)",
                    "city": "上海松江区",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 1,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZF4FAoILHeA4gRvWI",
                "_score": 1,
                "_source": {
                    "communityname": "保利西子湾",
                    "city": "上海松江大学城",
                    "age": 10,
                    "creationdate": "2008-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFmeqoILHeA4gRvWH",
                "_score": 1,
                "_source": {
                    "communityname": "金地格林世界",
                    "city": "上海嘉定南翔",
                    "age": 7,
                    "creationdate": "2011-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEy0foILHeA4gRvWE",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(宝山)",
                    "city": "上海宝山区",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            }
        ]
    },
    "aggregations": {
        "my_stats_age": {
            "count": 18,
            "min": 3,
            "max": 19,
            "avg": 7.944444444444445,
            "sum": 143
        }
    }
}

当然我们也能查询这里面的单个聚合统计值。比如

查询最小:

请求:

POST http://localhost:9200/rent/community/\_search

{
    "aggs":{
        "my_stats_age":{
            "min":{
                "field":"age"
            }
        }
    }
}

结果只有一个min

{
    "took": 74,
    "timed_out": false,
    "_shards": {
        "total": 3,
        "successful": 3,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": 18,
        "max_score": 1,
        "hits": [
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLY7wnkoILHeA4gRvV8",
                "_score": 1,
                "_source": {
                    "communityname": "万科阳光苑",
                    "city": "上海",
                    "age": 10,
                    "creationdate": "2008-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZDK3-oILHeA4gRvWA",
                "_score": 1,
                "_source": {
                    "communityname": "万科清林径",
                    "city": "上海浦东新区新场镇",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZD_9YoILHeA4gRvWC",
                "_score": 1,
                "_source": {
                    "communityname": "万科公园大道",
                    "city": "上海老闵行",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFA5EoILHeA4gRvWF",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(松江)",
                    "city": "上海松江区",
                    "age": 3,
                    "creationdate": "2015-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEXNioILHeA4gRvWD",
                "_score": 1,
                "_source": {
                    "communityname": "万科白马花园",
                    "city": "上海莘闵别墅区",
                    "age": 15,
                    "creationdate": "2003-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZGp3aoILHeA4gRvWM",
                "_score": 1,
                "_source": {
                    "communityname": "中海万锦城(二期)",
                    "city": "上海闸北不夜城",
                    "age": 6,
                    "creationdate": "2012-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZC1HYoILHeA4gRvV_",
                "_score": 1,
                "_source": {
                    "communityname": "万科优诗美地",
                    "city": "上海闵行七宝",
                    "age": 19,
                    "creationdate": "1999-01-01 00:00:00"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZF4FAoILHeA4gRvWI",
                "_score": 1,
                "_source": {
                    "communityname": "保利西子湾",
                    "city": "上海松江大学城",
                    "age": 10,
                    "creationdate": "2008-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZFmeqoILHeA4gRvWH",
                "_score": 1,
                "_source": {
                    "communityname": "金地格林世界",
                    "city": "上海嘉定南翔",
                    "age": 7,
                    "creationdate": "2011-01-01"
                }
            },
            {
                "_index": "rent",
                "_type": "community",
                "_id": "AWLZEy0foILHeA4gRvWE",
                "_score": 1,
                "_source": {
                    "communityname": "金地艺境(宝山)",
                    "city": "上海宝山区",
                    "age": 6,
                    "creationdate": "2012-01-01 00:00:00"
                }
            }
        ]
    },
    "aggregations": {
        "my_stats_age": {
            "value": 3
        }
    }
}
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Stella981 Stella981
3年前
Python进阶_mysql_查询、事物(4)
在进行查询之前,我们要先建好关系表,并往数据表中插入些数据。为查询操作做好准备。五张关系表的创建:创建并进入数据库:mysqlCREATEDATABASEinfo;QueryOK,1rowaffected(0.00sec)mysqlUSEinfo;Data
Wesley13 Wesley13
3年前
Oracle中如何查询一个表的所有字段名和数据类型
Oracle中如何查询一个表的所有字段名和数据类型查询语法selectA.COLUMN_NAME,A.DATA_TYPEfromuser_tab_columnsAwhereTABLE_NAME'表名'查询例子selectA.COLUMN_NAME,A.DATA_TYPEfromuser_tab_
Wesley13 Wesley13
3年前
MySQL总结(十一)子查询
!(https://oscimg.oschina.net/oscnet/upa344f41e81d3568e3310b5da00c57ced8ea.png)子查询1\.什么是子查询需求:查询开发部中有哪些员工selectfromemp;通
Wesley13 Wesley13
3年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
3年前
oracle查询表数据并重新插入到本表
oracle查询表数据并重新插入到本表CreateTime2018年5月17日10:30:10Author:Marydon1.情景描述查询表中数据SELECTFROMat_aut
Wesley13 Wesley13
3年前
mysql数据库查询操作
\mysql数据库\知识要点:1\.单表查询2\.子查询3\.联表查询4\.事务在进行查询之前,我们要先建好关系表,并往数据表中插入些数据。为查询操作做好准备。\五张关系表的创建:\\\mysql创建并进入数据库:mysqlCREATEDATABASE\
Stella981 Stella981
3年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型