Elasticsearch - 如何找出嵌套对象的字段值大于 X 且最大日期的所有文档

如果公司数量足够少，您不需要对它们进行分页，则可以使用组合和聚合来完成。基本上，它将找到最新报告，并将删除所有没有足够现金的公司。top_hitsbucket_selectortop_hitsbucket_selector

PUT test
{
  "mappings": {
    "properties": {
      "id": {
        "type": "long"
      },
      "financials": {
        "type": "nested",
        "properties": {
          "periodEndDate": {
            "type": "date"
          },
          "cash": {
            "type":"long"
          },
          "inventory": {
            "type": "long"
          }
        }
      }
    }
  }
}

POST test/_bulk?refresh
{"index":{"_id":1}}
{"id":1,"financials":[{"periodEndDate":"2022-06-30","cash":10000,"inventory":24000},{"periodEndDate":"2022-12-31","cash":12000,"inventory":19000}]}
{"index":{"_id":2}}
{"id":2,"financials":[{"periodEndDate":"2022-12-31","cash":200000,"inventory":1590000},{"periodEndDate":"2023-03-31","cash":210000,"inventory":1430000}]}

POST test/_search
{
  "size": 0,
  "aggs": {
    "companies": {
      "terms": {
        "field": "id"
      },
      "aggs": {
        "nested": {
          "nested": {
            "path": "financials"
          },
          "aggs": {
            "last_cash": {
              "top_hits": {
                "sort": [
                  {
                    "financials.periodEndDate": {
                      "order": "desc"
                    }
                  }
                ],
                "_source": {
                  "includes": [
                    "financials.cash",
                    "financials.periodEndDate"
                  ]
                },
                "size": 1
              }
            }
          }
        },
        "having.top_cash": {
          "bucket_selector": {
            "buckets_path": {
              "last_cash": "nested>last_cash[_source.cash]"
            },
            "script": "params.last_cash > 200000"
          }
        }
      }
    }
  }
}

此方法有几个局限性，包括可伸缩性差、可以处理的公司数量有限以及不支持第二旧的报告。如果您还需要按相关性对公司进行排序或浏览它们，这也不是最好的解决方案。

为了实现所有这些功能并构建可扩展的解决方案，我建议在索引期间添加额外的处理，并将最新报表和第二个报表索引为主对象中的专用字段，而不是将它们存储为嵌套并尝试在搜索期间查找最新和第二个最新。因此，基本上您的记录应如下所示：

  {
    "id": 1,
    "latest_finanicals": {
            "periodEndDate": "2022-12-31",
            "cash": 12000,
            "inventory": 19000
        }
    },
    "previous_finanicals": {
            "periodEndDate": "2022-06-30",
            "cash": 10000,
            "inventory": 24000
        }
    }
    "financials": [
      {
        "periodEndDate": "2022-06-30",
        "cash": 10000,
        "inventory": 24000
      },
      {
        "periodEndDate": "2022-12-31",
        "cash": 12000,
        "inventory": 19000
      }
    ]
  }

with 和 having type 而不是 type。然后，您的所有查询都将变得非常简单。latest_finanicalsprevious_finanicalsobjectnested