# Wrong counts using nested aggregation with filter having both parent and nested fields

**URL:** <https://discuss.elastic.co/t/wrong-counts-using-nested-aggregation-with-filter-having-both-parent-and-nested-fields/23186>\
**Category:** Elasticsearch\
**Created:** [April 10, 2015, 3:14pm UTC](https://discuss.elastic.co/t/wrong-counts-using-nested-aggregation-with-filter-having-both-parent-and-nested-fields/23186 "2015-04-10T15:14:03Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![thanuja](https://avatars.discourse-cdn.com/v4/letter/t/b487fb/32.png) [@thanuja](https://discuss.elastic.co/u/thanuja)\
**Post date:** [April 10, 2015, 3:14pm UTC](https://discuss.elastic.co/t/wrong-counts-using-nested-aggregation-with-filter-having-both-parent-and-nested-fields/23186/1 "2015-04-10T15:14:03Z")

</div>

I have a catalog of products that I want to calculate aggregates on. The  
trouble comes with trying to do nested aggregations with filter that has  
both nested and parent fields in it. Either it gives wrong counts or 0  
hits. Here is a sample of my product object mapping:

```
"Products": {
        "properties": {
           "ProductID": {
              "type": "long"
           },
           "ProductType": {
              "type": "long"
           },
           "ProductName": {
              "type": "string",
              "fields": {
                 "raw": {
                    "type": "string",
                    "index": "not_analyzed"
                 }
              }
           },
           "Prices": {
              "type": "nested",
              "properties": {
                 "CurrencyType": {
                    "type": "integer"
                 },
                 "Cost": {
                    "type": "double"                        
                 }
              }
          } 
      }
  }

```

Here is an example of the sql query that I am trying to replicate in  
elastic:

```
SELECT PRODPR.Cost AS PRODPR_Cost 
,COUNT(PROD.ProdcutID) AS PROD_ProductID_Count
FROM Products PROD WITH (NOLOCK)
LEFT OUTER JOIN Prices PRODPR WITH (NOLOCK) ON (PRODPR.objectid = 

```

PROD.objectid)  
WHERE PRODPR.CurrencyType = 4  
AND PROD.ProductType IN (  
11273  
,11293  
,11294  
)  
GROUP BY PRODPR.Cost  
Elastic Search queries I came up with:  
First One (following query returns correct counts with just CurrencyType as  
filter but when I add ProductType filter, it gives me wrong counts)

GET /IndexName/Products/\_search  
{  
"aggs": {  
"price\_agg": {  
"filter": {  
"bool": {  
**"must": [  
{  
"nested": {  
"path": "Prices",  
"filter": {  
"term": {  
"Prices.CurrencyType": "8"  
}  
}  
}  
},  
{  
"terms": {  
"ProductType": [  
"11273",  
"11293",  
"11294"  
]  
}  
}  
]**  
}  
},  
"aggs": {  
"price\_nested\_agg": {  
"nested": {  
"path": "Prices"  
},  
"aggs": {  
"59316518\_group\_agg": {  
"terms": {  
"field": "Prices.Cost",  
"size": 0  
},  
"aggs": {  
"product\_count": {  
"reverse\_nested": { },  
"aggs": {  
"ProductID\_count\_agg": {  
"value\_count": {  
"field": "ProductID"  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
},  
"size": 0  
}  
Second One (following query returns correct counts with just CurrencyType  
as filter but when I add ProductType filter, it gives me 0 hits):

GET /IndexName/Prodcuts/\_search  
{  
"aggs": {  
"price\_agg": {  
"nested": {  
"path": "Prices"  
},  
"aggs": {  
"currency\_filter": {  
"filter": {  
"bool": {  
"must": [  
{  
"term": {  
"Prices.CurrrencyType": "4"  
}  
},  
{  
"terms": {  
"ProductType": [  
"11273",  
"11293"  
]  
}  
}  
]  
}  
},  
"aggs": {  
"59316518\_group\_agg": {  
"terms": {  
"field": "Prices.Cost",  
"size": 0  
},  
"aggs": {  
"product\_count": {  
"reverse\_nested": {},  
"aggs": {  
"ProductID\_count\_agg": {  
"value\_count": {  
"field": "ProductID"  
}  
}  
}  
}  
}  
}  
}  
}  
}  
}  
},  
"size": 0  
}  
I have tried some more queries but the above two are the closest I came up  
with. Has anyone come across this use case? What am I doing wrong? Any help  
is appreciated. Thanks!

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/21bd1da6-177c-426d-a282-80b3ccfd5112%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/21bd1da6-177c-426d-a282-80b3ccfd5112%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 12:20am UTC](https://discuss.elastic.co/t/wrong-counts-using-nested-aggregation-with-filter-having-both-parent-and-nested-fields/23186/2 "2017-07-06T00:20:21Z")

</div>