Nested Aggregations returns wrong counts. Any help appreciated

Elastic Stack Elasticsearch
1

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": [ ---------------- Parent
field
"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": [ ---------------- Parent
field
"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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a1905997-8ddf-44f0-9623-f272ef33c164%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.