ES Aggs count distinct group by聚合排序查询

ES Aggs count distinct group by聚合排序查询

1. kibana query hits限制了10000条

添加
“track_total_hits”: true
query:

2. 查询返回特定字段

“_source”:[“includes”:[“oid”,“seq”,“ts”]]

3. 查询默认只返回10条数据

“size”: 100

4. sort排序

5. 分页from,size

6. aggs聚合

如果aggs，fiter，sort的字段是text，则解决方法1需要写成user_id.keyword，方法2：set fielddata=true不建议此方法；

text默认分词了，并未建索引，不允许进行聚合，排序,过滤

keyword类型 默认不分词的数据类型，常常被用来过滤、排序和聚合。
text类型 在存储数据的时候会默认进行分词，不能用来过滤、排序和聚合等操作。
使用聚合查询时不能使用分词，因此字段需要设置为type = FieldType.Keyword

相当于select distinct(user_id) from xxx where user_id_type=3

GET idx_test_query/_search
{
   "_source": ["id","goodsName"],
	  "query": {
	    "term": {
	      "user_id_type": 3
	    }
	  },
	  "collapse": {
	    "field": "user_id"
	  },
	 "sort": [
    {
      "_score": "desc"
    }
   ],
	 "from": 0,
	 "size": 100
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

多条件查询: select distinct(o_id) from xxx where s_type=10001 and (c_flag=3 or c_flag=4) and ts>=1681056000000 and ts <=1681142399000

GET idx_test_query/_search
{
    "_source":[
        "o_id"
    ],
    "query":{
        "bool":{
            "must":[
                {
                    "match":{
                        "s_type":"100001"
                    }
                },
                {
                    "bool":{
                        "should":[
                            {
                                "term":{
                                    "c_flag":4
                                }
                            },
                            {
                                "term":{
                                    "c_flag":4
                                }
                            }
                        ]
                    }
                }
            ],
            "filter":{
                "range":{
                    "ts":{
                        "gte":1681056000000,
                        "le":1681142399000
                    }
                }
            }
        }
    },
    "collapse":{
        "field":"o_id"
    },
    "sort":[
        {
            "_score":"desc"
        }
    ],
    "from":0,
    "size":100
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

报错

当user_id为text时，上述语句会报错org.elasticsearch.ElasticsearchException: Elasticsearch exception [type=illegal_argument_exception, reason=Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [idtest] in order to load field data by uninverting the inverted index. Note that this can use significant memory.]

解决办法1

{
	"_source": ["id","user_id"],
	"query": {
	   "term": {
		 "user_id_type": 3
	   }
	},
	"collapse": {
	   "field": "user_id.keyword"
	},
	"sort":{
	},
	"from": 0,
	"size": 100
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

解决办法2 不推荐

在[user_id]上设置fielddata=true，以便通过取消反转索引来加载字段数据
请注意，这可能会占用大量内存

POST /idx_test_query/_mapping
{
	"properties": {
		"user_id": {
			"type": "text",
			"fielddata": true
		}
	}
}
1
2
3
4
5
6
7
8
9

https://blog.csdn.net/zhuchunyan_aijia/article/details/122901327
https://blog.csdn.net/weixin_45204847/article/details/117423652

参考

• 查询query，range，指定source
• http://www.noobyard.com/article/p-cuojtyia-bp.html
• index.max_result_window
• Elasticsearch7.x聚合查询发生异常Text fields are not optimised
• https://cloud.tencent.com/developer/article/2024062