elasticsearch - 从ElasticSearch查询未售出的产品

Question

我是ElasticSearch的新手，需要一些帮助...

我有一个名为sales的索引，其中包含以下记录类型:

{product.id : 12 , sales.datetime : October 12th 2019},
{product.id : 13 , sales.datetime : October 12th 2019},
{product.id : 14 , sales.datetime : October 13th 2019},
{product.id : 14 , sales.datetime : October 14th 2019},
{product.id : 14 , sales.datetime : October 14th 2019},
{product.id : 13 , sales.datetime : October 18th 2019},
                             ...

我想检索自2019年10月12日以来未售出的产品，

我尝试使用以下方法过滤sales.datetime:

"range" => [
             "datetime" => [
                    "lt" => "October 12th 2019"
                ]
            ] 

但是显然，这种查询将返回意外的值，例如在这种情况下，它将检索 product.id : [12,13]，但您可以看到product.id已于2019年10月18日出售...

Answer 1

对应:

PUT sales
{
  "mappings": {
    "properties": {
      "id":{
        "type": "keyword"
      },
      "date":{
        "type": "date",
        "format": "MM-dd-yyyy" 
      }
    }
  }
}

数据:

 [
      {
        "_index" : "sales",
        "_type" : "_doc",
        "_id" : "I-6Y7W0B_-hMjUaqpQH4",
        "_score" : 1.0,
        "_source" : {
          "id" : 12,
          "date" : "10-12-2019"
        }
      },
      {
        "_index" : "sales",
        "_type" : "_doc",
        "_id" : "JO6Y7W0B_-hMjUaqtwEL",
        "_score" : 1.0,
        "_source" : {
          "id" : 13,
          "date" : "10-12-2019"
        }
      },
      {
        "_index" : "sales",
        "_type" : "_doc",
        "_id" : "Je6Y7W0B_-hMjUaqxgEH",
        "_score" : 1.0,
        "_source" : {
          "id" : 13,
          "date" : "10-18-2019"
        }
      }
    ]

查询:-获取所有术语的最大日期，如果日期范围小于2019-10-12，则获取最大日期
如果两个都是相同的返回桶

GET sales/_search
{
  "size": 0,
  "aggs": {
    "transactionId": {
      "terms": {
        "field": "id",
        "size": 10000
      },
      "aggs": {
        "maxDate": {
          "max": {
            "field": "date"
          }
        },
        "pending_status": {
          "filter": {
            "range": {
              "date": {
                "lte": "10-12-2019"
              }
            }
          },
          "aggs": {
            "filtered_maxdate": {
              "max": {
                "field": "date"   
              }
            }
          }
        },
        "buckets_latest_status_pending": {
          "bucket_selector": {
            "buckets_path": {
              "filtereddate": "pending_status>filtered_maxdate",
              "maxDate": "maxDate"
            },
            "script": "params.filtereddate==params.maxDate"
          }
        }
      }
    }
  }
}

响应:

 [
        {
          "key" : "12",
          "doc_count" : 1,
          "pending_status" : {
            "doc_count" : 1,
            "filtered_maxdate" : {
              "value" : 1.5708384E12,
              "value_as_string" : "10-12-2019"
            }
          },
          "maxDate" : {
            "value" : 1.5708384E12,
            "value_as_string" : "10-12-2019"
          }
        }
      ]

编辑1:

您可以使用(top_hits)[ https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html]聚合将存储桶中的所有文档

GET sales/_search
{
  "size": 0,
  "aggs": {
    "transactionId": {
      "terms": {
        "field": "id",
        "size": 10000
      },
      "aggs": {
        "maxDate": {
          "max": {
            "field": "date"
          }
        },
        "pending_status": {
          "filter": {
            "range": {
              "date": {
                "lte": "10-12-2019"
              }
            }
          },
          "aggs": {
            "filtered_maxdate": {
              "max": {
                "field": "date"   
              }
            }
          }
        },
        "buckets_latest_status_pending": {
          "bucket_selector": {
            "buckets_path": {
              "filtereddate": "pending_status>filtered_maxdate",
              "maxDate": "maxDate"
            },
            "script": "params.filtereddate==params.maxDate"
          }
        },
        "top_hits":{   ---> top hits to get all documents under a bucket
          "top_hits": {
            "size": 10
          }
        }
      }
    }
  }
}

对于分页，您可以使用 composite aggregation / include partitions