同一请求中的 Elasticsearch 组、过滤器和聚合

Question

我有一个用例，用于在 Elasticsearch 中按两个单独的字段进行过滤和分组，同时聚合其余字段中的值。寻求帮助制定 Elasticsearch 查询。这是一个例子。每个文档都有一个 locationId、category、productId、visitCount、purchaseCount 和 <代码>样本计数。我想查看每个位置中每个类别的 visitCount、purchaseCount 和 sampleCount 总和。请注意，productId 在所有条目中都是唯一的。我尝试阅读 Elasticsearch 文档，但找不到好的资源来学习如何进行分组、过滤和聚合。请注意，这是一个网站用例，我们在带有页面的表格中显示这些数据。由于位置和类别的数量，很可能会有多个组超出一页。请帮助制定 Elasticsearch 查询。

示例文档：

[{
    "locationId": 12345,
    "category": "Food",
    "productId": "JKHNG98",
    "visitCount": 10,
    "purchaseCount": 9,
    "sampleCount": 7
}, {
    "locationId": 12345,
    "category": "Food",
    "productId": "HJUSY68",
    "visitCount": 1,
    "purchaseCount": 15,
    "sampleCount": 7
}, {
    "locationId": 12345,
    "category": "Entertainment",
    "productId": "KGUJKHG78",
    "visitCount": 20,
    "purchaseCount": 15,
    "sampleCount": 10
}, {
    "locationId": 12345,
    "category": "Entertainment",
    "productId": "67912HYK",
    "visitCount": 5,
    "purchaseCount": 15,
    "sampleCount": 10
}, {
    "locationId": 54321,
    "category": "Food",
    "productId": "9823HYKN",
    "visitCount": 15,
    "purchaseCount": 12,
    "sampleCount": 5
}, {
    "locationId": 54321,
    "category": "Food",
    "productId": "KJHKJSAHD22",
    "visitCount": 55,
    "purchaseCount": 12,
    "sampleCount": 5
}, {
    "locationId": 54321,
    "category": "Entertainment",
    "productId": "SDJFHSF788",
    "visitCount": 45,
    "purchaseCount": 44,
    "sampleCount": 23
}, {
    "locationId": 54321,
    "category": "Entertainment",
    "productId": "2131286JH",
    "visitCount": 80,
    "purchaseCount": 44,
    "sampleCount": 23
}]

输入可以是多个位置 ID，但始终只有 1 个类别。

过滤器输入类别“食品”的预期结果：

[{
    "locationId": 12345,
    "category": "Food",
    "sumOfVisitCount": 11,
    "sumOfPurchaseCount": 24,
    "sumOfSampleCount": 14
},{
    "locationId": 54321,
    "category": "Food",
    "sumOfVisitCount": 15,
    "sumOfPurchaseCount": 12,
    "sumOfSampleCount": 5
}]

位置“12345”的过滤器输入的预期结果：

[{
    "locationId": 12345,
    "category": "Food",
    "sumOfVisitCount": 11,
    "sumOfPurchaseCount": 24,
    "sumOfSampleCount": 14
}, {
    "locationId": 12345,
    "category": "Entertainment"
    "sumOfVisitCount": 25,
    "sumOfPurchaseCount": 30,
    "sumOfSampleCount": 20
}]

位置“12345”和类别“食品”的过滤器输入的预期结果：

[{
    "locationId": 12345,
    "category": "Food",
    "sumOfVisitCount": 11,
    "sumOfPurchaseCount": 24,
    "sumOfSampleCount": 14
}]

Answer 1

您需要结合使用查询和聚合来获得所需的结果。以下查询使用：

过滤器输入类别“食品”的搜索查询

1. 查询部分使用术语查询 匹配类别等于 Food
2. 使用术语聚合 ，根据 locationId 创建存储桶，并查找 sampleCount、viewCount 和 purchaseCount 的总和， <一使用 href="https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html" rel="nofollow noreferrer">求和聚合

POST idxtest/_search
{
  "size": 0,
  "query": {
    "term": {
      "category.keyword": "Food"
    }
  },
  "aggs": {
    "NAME": {
      "terms": {
        "field": "locationId"
      },
      "aggs": {
        "sumOfPurchaseCount": {
          "sum": {
            "field": "purchaseCount"
          }
        },
        "sumOfVisitCount": {
          "sum": {
            "field": "visitCount"
          }
        },
        "sumOfSampleCount": {
          "sum": {
            "field": "sampleCount"
          }
        }
      }
    }
  }
}

搜索结果：

"aggregations" : {
    "NAME" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 12345,
          "doc_count" : 2,
          "sumOfSampleCount" : {
            "value" : 14.0
          },
          "sumOfVisitCount" : {
            "value" : 11.0
          },
          "sumOfPurchaseCount" : {
            "value" : 24.0
          }
        },
        {
          "key" : 54321,
          "doc_count" : 2,
          "sumOfSampleCount" : {
            "value" : 10.0
          },
          "sumOfVisitCount" : {
            "value" : 70.0
          },
          "sumOfPurchaseCount" : {
            "value" : 24.0
          }
        }
      ]
    }

位置“12345”和类别“食品”的过滤器输入的搜索查询

POST idxtest/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "category.keyword": "Food"
          }
        },
        {
          "term": {
            "locationId": 12345
          }
        }
      ]
    }
  },
  "aggs": {
    "NAME": {
      "terms": {
        "field": "locationId"
      },
      "aggs": {
        "sumOfPurchaseCount": {
          "sum": {
            "field": "purchaseCount"
          }
        },
        "sumOfVisitCount": {
          "sum": {
            "field": "visitCount"
          }
        },
        "sumOfSampleCount": {
          "sum": {
            "field": "sampleCount"
          }
        }
      }
    }
  }
}

搜索结果：

"aggregations" : {
    "NAME" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 12345,
          "doc_count" : 2,
          "sumOfSampleCount" : {
            "value" : 14.0
          },
          "sumOfVisitCount" : {
            "value" : 11.0
          },
          "sumOfPurchaseCount" : {
            "value" : 24.0
          }
        }
      ]
    }
  }