elastic 执行复杂查询

Question

elastic 中显示参数
底下这是公司的执行的搜索语句

post eops-supervision-slow-mysql-2023.02.27-2023.03.05/_doc/_search
{
    "from": 0,
    "size": 1,
    "query": {
        "bool": {
            "filter": [{
                "match": {
                    "hasSupervision": {
                        "query": 0,
                        "operator": "OR",
                        "prefix_length": 0,
                        "max_expansions": 50,
                        "fuzzy_transpositions": true,
                        "lenient": false,
                        "zero_terms_query": "NONE",
                        "auto_generate_synonyms_phrase_query": true,
                        "boost": 1.0
                    }
                }
            }, {
                "range": {
                    "queryTx": {
                        "from": "2023-02-26T15:47:00",
                        "to": "2023-02-27T15:57:00",
                        "include_lower": false,
                        "include_upper": true,
                        "boost": 1.0
                    }
                }
            }],
            "must_not": [{
                "term": {
                    "appsysid": {
                        "value": 1,
                        "boost": 1.0
                    }
                }
            }],
            "adjust_pure_negative": true,
            "boost": 1.0
        }
    },
    "version": true,
    "aggregations": {
        "by_time": {
            "date_histogram": {
                "field": "queryTx",
                "format": "8yyyy-MM-dd HH:mm:ss",
                "interval": "300s",
                "offset": 56820000,
                "order": {
                    "_key": "asc"
                },
                "keyed": false,
                "min_doc_count": 1
            },
            "aggregations": {
                "groupByDbId": {
                    "terms": {
                        "field": "dbId",
                        "size": 2147483647,
                        "min_doc_count": 1,
                        "shard_min_doc_count": 0,
                        "show_term_doc_count_error": false,
                        "order": [{
                            "_count": "desc"
                        }, {
                            "_key": "asc"
                        }]
                    },
                    "aggregations": {
                        "groupByFingerId": {
                            "terms": {
                                "field": "fingerId",
                                "size": 2147483647,
                                "min_doc_count": 1,
                                "shard_min_doc_count": 0,
                                "show_term_doc_count_error": false,
                                "order": [{
                                    "_count": "desc"
                                }, {
                                    "avg_time": "desc"
                                }, {
                                    "_key": "asc"
                                }]
                            },
                            "aggregations": {
                                "avg_time": {
                                    "avg": {
                                        "field": "queryTime"
                                    }
                                },
                                "having": {
                                    "bucket_selector": {
                                        "buckets_path": {
                                            "avgTime": "avg_time",
                                            "exeCount": "_count"
                                        },
                                        "script": {
                                            "source": "params.avgTime > 0.1 && params.exeCount > 1",
                                            "lang": "painless"
                                        },
                                        "gap_policy": "skip"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

这是显示结果

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 4,
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "eops-supervision-slow-mysql-2023.02.27-2023.03.05",
        "_type" : "_doc",
        "_id" : "94043279",
        "_version" : 1,
        "_score" : 0.0,
        "_source" : {
          "id" : 94043279,
          "dbId" : 13,
          "queryTx" : "2023-02-27T14:40:08",
          "slowSchema" : "eops",
          "md5Sql" : "6c2fbc5cb0c4edf496546398cca05eff0f712b56dc46cc8f2a5d9309708c2780",
          "queryLength" : "39",
          "queryTime" : 3.659591,
          "queryHost" : "10.11.0.221(8806)",
          "queryUser" : "eops",
          "slowSql" : "select * from mysql_supervision limit 1",
          "slowFinger" : "select * from `mysql_supervision` limit ?",
          "optmize" : "SET timestamp=1677480008,now()=1677485825575,sDateFormat=Mon Feb 27 14:40:08 CST 2023,slowMysql.getQueryTx()Mon Feb 27 14:40:08 CST 2023,insert之前的QueryTx====Mon Feb 27 14:40:08 CST 2023,insert之后的QueryTx====Mon Feb 27 14:40:08 CST 2023,offset====18",
          "fingerId" : 500721,
          "appsysid" : 21,
          "appsysidSource" : 4,
          "schemaSource" : 2,
          "appName" : null,
          "examplesName" : "10.11.0.221_8806",
          "dbType" : 1,
          "number" : 1,
          "avgTime" : null,
          "allTime" : null,
          "isBroadcast" : null,
          "lockTime" : "0.000175",
          "fullScan" : "否",
          "fullJoin" : "否",
          "filesortOnDisk" : "否",
          "filesort" : "否",
          "tmpTable" : "否",
          "tmpTableOnDisk" : "否",
          "checkLevel" : 0,
          "handleComment" : null,
          "rowsSent" : 0,
          "rowsExamined" : 1,
          "errorMsg" : "",
          "createTime" : "2023-02-27 16:17:27",
          "hasSupervision" : 0,
          "initialMsg" : null,
          "queryTxNum" : 1677480008000,
          "isWhite" : null
        }
      }
    ]
  },
  "aggregations" : {
    "by_time" : {
      "buckets" : [
        {
          "key_as_string" : "2023-02-27 14:37:00",
          "key" : 1677508620000,
          "doc_count" : 4,
          "groupByDbId" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : 13,
                "doc_count" : 4,
                "groupByFingerId" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : 500720,
                      "doc_count" : 2,
                      "avg_time" : {
                        "value" : 8.659590721130371
                      }
                    },
                    {
                      "key" : 500721,
                      "doc_count" : 2,
                      "avg_time" : {
                        "value" : 3.65959095954895
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  }
}

想让这个返回结果多显示一个参数，比如initialMsg，要怎么改查询语句呢

Answer 1

该回答引用GPTᴼᴾᴱᴺᴬᴵ
如果您想让返回结果中包含更多的字段，您可以在查询时指定需要的字段。例如，如果您想要在返回结果中包含 "initialMsg" 字段，您可以将以下代码添加到查询语句的 "_source" 字段中：

{
  "_source": ["id", "dbId", "queryTx", "slowSchema", "md5Sql", "queryLength", "queryTime", "queryHost", "queryUser", "slowSql", "slowFinger", "optmize", "fingerId", "appsysid", "appsysidSource", "schemaSource", "appName", "examplesName", "dbType", "number", "avgTime", "allTime", "isBroadcast", "lockTime", "fullScan", "fullJoin", "filesortOnDisk", "filesort", "tmpTable", "tmpTableOnDisk", "checkLevel", "handleComment", "rowsSent", "rowsExamined", "errorMsg", "createTime", "hasSupervision", "initialMsg", "queryTxNum", "isWhite"]
}

注意，为了包含 "initialMsg" 字段，您需要将其添加到字段列表中。如果您只需要返回所有字段，您可以将 "_source" 字段设置为 "true"，例如：

{
  "_source": true
}

请注意，为了在 Elasticsearch 查询中包含这些字段，您必须使用包含这些字段的索引。如果索引中不包含这些字段，则查询不会返回它们。