I have a parent child relationship setup:
Customer (Parent) -> Order Line (Child)
I'm trying to count the number of parents (customers) that have children matching two sets of criteria. i.e.
I want the number of customers that have purchased both a Foo and a Bar.
At the moment, I can only get vaguely close to this by a complex series of bucketing, just using the child type.
GET customer_orderlines/orderline/_search
{
"size": 0,
"query": {
"bool": {
"should": [
{
"match": {
"prod_key": "foo"
}
},
{
"match": {
"prod_key": "bar"
}
}
]
}
},
"aggs": {
"customers": {
"terms": {
"field": "customer_key"
},
"aggs": {
"matching_products": {
"cardinality": {
"field": "prod_key"
}
},
"sales_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"matchingProducts": "matching_products"
},
"script": "params.matchingProducts > 1"
}
}
}
}
}
}
Whats the correct way of doing this? Basically im trying to emulate the following sort of SQL
SELECT Count(DISTINCT customer_key)
FROM (SELECT customer_key,
Count(DISTINCT product) AS prd_count
FROM orders
WHERE orders.product IN ( 'foo', 'bar' ))
WHERE prd_count > 1