时间戳

首页 > 解决方案 > Elasticsearch：当字段是数组时如何按字段对文档进行分组

arrays - Elasticsearch：当字段是数组时如何按字段对文档进行分组

问题描述

我的 Elasticsearch 索引如下所示：

{
    "team": ["Jane","Jason"],
    "date": "2020/07/23 12:00:56",
    "is_work_done": true
},
{
    "team": ["Jane","Jason"],
    "date": "2020/07/22 14:23:56",
    "is_work_done": false
},
{
    "team": ["Jane","Jason","Anna"],
    "date": "2020/07/17 09:22:10",
    "is_work_done": false
},
{
    "team": ["Alex","George","Anna"],
    "date": "2020/07/13 03:24:19",
    "is_work_done": true
}

我的映射是：

{
  "mappings": {
    "type_name": {
      "properties": {
        "team":    { "type": "keyword" },  
        "date":  {"type": "date", "format": "yyyy/MM/dd HH:mm:ss"},
        "is_work_done": { "type": "boolean" }
      }
    }
  }
}

我想收集每个团队的信息。如何按团队对文档进行分组？我创建了这个索引来解决这个问题，因为实际上，我不知道每个团队包含多少成员。

我试图聚合文档，但找不到适合的聚合类型。

例如，使用此查询：

GET /testbench-test/_search
{
  "aggs": {
    "mybucket": {
      "terms": { "field": "team" } 
    }
  }
}

我得到这个结果：

 "aggregations" : {
    "mybucket" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Jane",
          "doc_count" : 3
        },
        {
          "key" : "Jason",
          "doc_count" : 3
        },
        {
          "key" : "Anna",
          "doc_count" : 2
        },
        {
          "key" : "Alex",
          "doc_count" : 1
        },
        {
          "key" : "George",
          "doc_count" : 1
        }
      ]
    }
  }

感谢您的帮助！

编辑：查询包含 64,030 个索引的真实索引：

POST _search 
{
  "aggs": {
    "teams": {
      "terms": {
        "script": "doc['team'].join(' & ')",
        "size": 10
      }
    }
  }
}

我得到这个结果：

{
  "took" : 52,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 64031,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "98",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "9827af",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f930813",
          "date" : "2019/04/25 11:40:19",
          "duration" : 0.9953742847,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00013A9D00002"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "b0c9a8aa4",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "b0c9a1be0a8aa4",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf385a1562d78813",
          "date" : "2019/04/29 08:08:37",
          "duration" : 0.4976871423,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00149"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "bb1d525e2a368f6d4",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "bb1da368f6d4",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f85a1562d78813",
          "date" : "2019/04/29 08:09:51",
          "duration" : 0.5208305083,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00179"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "87bec43ce2553c590b",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "87bec43c-e2553c590b",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f9302bcdf8c26e1f85a1562d78813",
          "date" : "2019/04/29 08:10:10",
          "duration" : 0.4629604518,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00181"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "3499224bdac4fa39",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "349922444bdac4fa39",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6e1f85a1562d78813",
          "date" : "2019/04/29 08:10:49",
          "duration" : 0.5092588253,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "uut" : [
            "MCANT00009A9D00171"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "f8236de0-dd6a97b7a81",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "f8236de0-add6a97b7a81",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f93085a1562d78813",
          "date" : "2019/04/29 09:51:47",
          "duration" : 0.6134272553,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00221"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "339fa5a2e-cf9f1f4738bf",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "339fa5a9f1f4738bf",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f932d78813",
          "date" : "2019/04/29 09:51:57",
          "duration" : 0.6249989383,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00185"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "dbd45ec4-f53b-4bda-9eeb-dadf2e3ab366",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "dbd45ec4-f53b-4bda-9eeb-dadf2e3ab366",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f9302bcdfd78813",
          "date" : "2019/04/29 09:52:19",
          "duration" : 0.5787005648,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00184"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "bc0d3548bed68",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "bc0d354348bed68",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf3801562d78813",
          "date" : "2019/04/29 08:08:12",
          "duration" : 0.5208305083,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00160"
          ]
        }
      },
      {
        "_index" : "test-logs",
        "_type" : "log",
        "_id" : "d591b9e7-683a-4d78-be31-1b137b8a3b2b",
        "_score" : 1.0,
        "_source" : {
          "uuid" : "d591b9e7-683a-4d78-be31-1b137b8a3b2b",
          "benchId" : "m",
          "benchGroup" : "e",
          "machine" : "CH",
          "sha1" : "ddf380fd6a2f93013",
          "date" : "2019/04/29 08:08:05",
          "duration" : 0.6828689948,
          "isCss" : false,
          "isPassed" : true,
          "finalStatus" : "OK_VNA_TEST",
          "team" : [
            "MCANT00009A9D00146"
          ]
        }
      }
    ]
  },
  "aggregations" : {
    "teams" : {
      "doc_count_error_upper_bound" : 25,
      "sum_other_doc_count" : 63658,
      "buckets" : [
        {
          "key" : "SI050010AA8C00001",
          "doc_count" : 86
        },
        {
          "key" : "00845D9E0137 & 00851F9E0095 & CPPCB00608C9F00060 & MCSAS00676A9F00141_-27.8_B & MCSAS00677A9F00146_23.0_B & SI050012AG9F00060",
          "doc_count" : 56
        },
        {
          "key" : "00845D9E0275 & 00851F9G0056 & CPPCB00608C9F00127 & MCSAS00676B9G00012 & MCSAS00676B9G00012_-21.2_C & MCSAS00677B9G00005 & MCSAS00677B9G00005_32.5_C & SI050012AG9F00127",
          "doc_count" : 43
        },
        {
          "key" : "00844G9D0041 & 00847D9G0020 & MCANT00009A9G00048 & MCSAS00652F9E00091 & S20-17272 & SI050001AG9G00055 & SI050004AA9F00059",
          "doc_count" : 39
        },
        {
          "key" : "00845D9E0035 & 00851F9E0062 & CPPCB00608C9E00034 & MCSAS00676A9E00090_-35.0_B & MCSAS00677A9E00089_31.0_B & SI050012AG9E00034",
          "doc_count" : 34
        },
        {
          "key" : "IX & IX-c2-67063 & IX-x2-00511 & SI050010AA9A00002 & droneProduction",
          "doc_count" : 27
        },
        {
          "key" : "IX & IX-12-10251 & IX-x2-00484 & SI050001AF9A00020 & SI050010AA8J00154 & SI050012AG9D00082 & droneClient",
          "doc_count" : 25
        },
        {
          "key" : "MCANT00009A9G00048 & MCSAS00652F9E00091 & S20-17272 & SI050001AG9G00055",
          "doc_count" : 24
        },
        {
          "key" : "00883C0F0000",
          "doc_count" : 20
        },
        {
          "key" : "00844C8B0029 & 00847C8E0018 & 00849A8B0015 & MCANT00009A8E00017 & N/A & S20-00533 & SI050002AA8E000001 & SI050004AA8E000514",
          "doc_count" : 19
        }
      ]
    }
  }
}

为什么不是所有不同的“团队”都有自己的桶？

标签： arrayselasticsearchgroup-byfield

解决方案

您可以做的是在您的术语聚合中使用脚本，如下所示：

POST teams/_search 
{
  "size": 0,
  "aggs": {
    "teams": {
      "terms": {
        "script": "doc['team'].join('-')",
        "size": 10
      }
    }
  }
}

你将得到的结果是这样的：

  "buckets" : [
    {
      "key" : "Jane-Jason",
      "doc_count" : 2
    },
    {
      "key" : "Alex-Anna-George",
      "doc_count" : 1
    },
    {
      "key" : "Anna-Jane-Jason",
      "doc_count" : 1
    }
  ]

推荐阅读