ES nested 聚合统计仅符合过滤的子项

ES nested 统计仅符合的子项

1. 相似SQL
   select sum(item.sku_cnt) from table_main main
   left join table_item item on main.id = item.main_id
   where main.id in (4,5,6) and item.sku_id is not null;

2.ES queryDSL查询

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "terms": {
                        "id": [
                            4,
                            5,
                            6
                        ]
                    }
                }
            ]
        }
    },
    "aggs": {
        //nested子项聚合的需要指定nested路径
        "sumSkuNest": {
            "nested": {
                "path": "tableItem"
            },
            //过滤有sku_Id。在query块能筛选订单，但是要从只符合的子项统计的时候，子项兄弟不符合的还是会统计进来，所以要在这里加filter，过滤掉子项不符合的兄弟节点
            "aggs": {
                //AggregationBuilders.filter("filterSkuId", skuIdQueryBuilder)
                "filterSkuId": {
                    "filter": {
                        //skuIdQueryBuilder = QueryBuilders.existsQuery("tableItem.sku_Id");
                        "exists": {
                            "field": "tableItem.sku_Id"
                        }
                    },
                    //统计字段 AggregationBuilders.sum("sumSku").field("tableItem.sku_cnt")
                    "aggs": {
                        "sumSku": {
                            "sum": {
                                "field": "tableItem.sku_cnt"
                            }
                        }
                    }
                }
            }
        }
    }
}

3.对应的java API
WrapperQueryBuilder wrapperQuery = QueryBuilders.wrapperQuery(queryDSL);

            QueryBuilder agentStoreIdQueryBuilder = QueryBuilders.existsQuery("tableItem.agentStoreId");
            FilterAggregationBuilder agentStoreIdFilter = AggregationBuilders.filter("filterSkuId", agentStoreIdQueryBuilder)
                    .subAggregation(AggregationBuilders.sum("sumSku").field("tableItem.skuCnt"));

            NestedAggregationBuilder sumSkuBuilder = AggregationBuilders.nested("sumSkuNest", "tableItem")
                    .subAggregation(agentStoreIdFilter);

            NativeSearchQuery searchQuery = new NativeSearchQueryBuilder()
                    .withIndices(ocOrderIndexName)
                    .withTypes(ocOrderTypeName)
                    .withQuery(wrapperQuery)
                    .addAggregation(sumSkuBuilder)
                    .build();

            Aggregations aggregations = esTemplate.query(searchQuery, SearchResponse::getAggregations);

            InternalNested sumSkuNest = (InternalNested)aggregations.asMap().get("sumSkuNest");
            InternalFilter internalFilter = (InternalFilter) sumSkuNest.getAggregations().asMap().get("filterSkuId");

            InternalSum internalSum = internalFilter.getAggregations().get("sumSku");
            Double sumValue = internalSum.value();
            response.setContent(sumValue.longValue());