c# Elasticsearch统计Nested的数据

我是直接问百度ai

es统计是nested结构的数据，数据有年份：reportyear，名称：accountnamezh，类型：reportperiodtype，利润：originalvalue，，需要统计：2024年，名称：营业收入,类型：2，的利润合计是多少，利润金额比较大，需要除以100000000

然后生成的es是

POST /your_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "financial_data",
            "query": {
              "bool": {
                "must": [
                  { "term": { "financial_data.reportyear": 2024 } },
                  { "term": { "financial_data.accountnamezh": "营业收入" } },
                  { "term": { "financial_data.reportperiodtype": 2 } }
                ]
              }
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "total_profit_in_millions": {
      "nested": {
        "path": "financial_data"
      },
      "aggs": {
        "sum_profit": {
          "sum": {
            "script": {
              "source": "doc['financial_data.originalvalue'].value / 100000000"
            }
          }
        }
      }
    }
  }
}

生成c#代码

using Nest;
using System;
using System.Threading.Tasks;

public class ElasticsearchDocument
{
    public NestedField FinancialData { get; set; }
}

public class FinancialData
{
    public int ReportYear { get; set; }
    public string AccountNameZh { get; set; }
    public int ReportPeriodType { get; set; }
    public double OriginalValue { get; set; }
}

// 注意：这里的NestedField和Elasticsearch的嵌套字段类型相对应，
// 但实际上NEST中的NestedField通常用于查询时的映射，而不是文档模型的一部分。
// 在查询时，我们会用到一个与FinancialData结构相似的匿名类型来构建查询。
// 这里的模型主要是为了展示目的。

class Program
{
    private static ElasticClient _client;

    static async Task Main(string[] args)
    {
        _client = new ElasticClient(settings: new ConnectionSettings(new Uri("http://localhost:9200")));

        var response = await _client.SearchAsync<ElasticsearchDocument>(s => s
            .Index("your_index")
            .Query(q => q
                .Nested(n => n
                    .Path(p => p.FinancialData)
                    .Query(nq => nq
                        .Bool(b => b
                            .Must(m => m
                                .Term(t => t
                                    .Field(f => f.FinancialData.ReportYear)
                                    .Value(2024))
                                .Term(t => t
                                    .Field(f => f.FinancialData.AccountNameZh)
                                    .Value("营业收入"))
                                .Term(t => t
                                    .Field(f => f.FinancialData.ReportPeriodType)
                                    .Value(2)))))
            .Aggregations(a => a
                .Nested("total_profit_in_millions", n => n
                    .Path(p => p.FinancialData)
                    .Aggregations(na => na
                        .Sum("sum_profit", s => s
                            .Script(sc => sc
                                .Source("doc['financial_data.original_value'].value / 100000000")))))))
            .Size(0)); // 我们不需要实际的文档，只需要聚合结果

        // 处理响应
        if (response.IsValid)
        {
            var totalProfitInMillions = response.Aggregations.Nested("total_profit_in_millions")
                ?.Sum("sum_profit")
                ?.Value;

            Console.WriteLine($"Total Profit in Millions: {totalProfitInMillions}");
        }
        else
        {
            Console.WriteLine("Search failed: " + response.ServerError.Error.Reason);
        }
    }
}

然后在这个基础上调整就完成了

在加一个按主表字段分组

{
    "from": 0,
    "size": 0,
    "aggs": {
        "by_city": {
            "terms": {
                "field": "cityname.keyword",  //主表字段
                "size": 10000
            },
            "aggs": {
                "companyfeaturelist": {
                    "nested": {
                        "path": "companyfeaturelist"
                    },
                    "aggs": {
                        "filtereddata": {
                            "filter": {
                                "bool": {
                                    "must": [
                                        {
                                            "bool": {
                                                "must": [
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.reportyear.keyword": [
                                                                2024
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.accountnamezh.keyword": [
                                                                "营业收入"
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.reportperiodtype": [
                                                                2
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.typeid": [
                                                                22
                                                            ]
                                                        }
                                                    }
                                                ]
                                            }
                                        }
                                    ]
                                }
                            },
                            "aggs": {
                                "scaled_profit": {
                                    "scripted_metric": {
                                        "init_script": {
                                            "inline": "state.sum = 0.0"
                                        },
                                        "map_script": {
                                            "inline": "state.sum += doc['companyfeaturelist.originalvalue'].value / 100000000"
                                        },
                                        "combine_script": {
                                            "inline": "return state.sum"
                                        },
                                        "reduce_script": {
                                            "inline": "double sum = 0.0; for (t in states) { sum += t } return sum"
                                        }
                                    }
                                }
                            }
                        },
                        "filteredLastdata": {
                            "filter": {
                                "bool": {
                                    "must": [
                                        {
                                            "bool": {
                                                "must": [
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.reportyear.keyword": [
                                                                2023
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.accountnamezh.keyword": [
                                                                "营业收入"
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.reportperiodtype": [
                                                                2
                                                            ]
                                                        }
                                                    },
                                                    {
                                                        "terms": {
                                                            "companyfeaturelist.typeid": [
                                                                22
                                                            ]
                                                        }
                                                    }
                                                ]
                                            }
                                        }
                                    ]
                                }
                            },
                            "aggs": {
                                "scaled_profit": {
                                    "scripted_metric": {
                                        "init_script": {
                                            "inline": "state.sum = 0.0"
                                        },
                                        "map_script": {
                                            "inline": "state.sum += doc['companyfeaturelist.originalvalue'].value / 100000000"
                                        },
                                        "combine_script": {
                                            "inline": "return state.sum"
                                        },
                                        "reduce_script": {
                                            "inline": "double sum = 0.0; for (t in states) { sum += t } return sum"
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    "query": {   //查询条件
        "bool": {
            "filter": [
                {
                    "term": {
                        "isdelete": {
                            "value": 0
                        }
                    }
                }
            ]
        }
    }
}