# Get documents where sum of values in filtered nested documents match a specified conditions
**URL:** https://discuss.elastic.co/t/get-documents-where-sum-of-values-in-filtered-nested-documents-match-a-specified-conditions/187818
**Category:** Elasticsearch
**Created:** [June 27, 2019, 11:56am UTC](https://discuss.elastic.co/t/get-documents-where-sum-of-values-in-filtered-nested-documents-match-a-specified-conditions/187818 "2019-06-27T11:56:45Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@radix\_s](https://discuss.elastic.co/u/radix_s)
#### Post date: [June 27, 2019, 11:56am UTC](https://discuss.elastic.co/t/get-documents-where-sum-of-values-in-filtered-nested-documents-match-a-specified-conditions/187818/1 "2019-06-27T11:56:45Z")
I am tracking orders made by the shop visitors and for each order I put a document that goes into an index like this:
```
DELETE visitor_carts
PUT visitor_carts
{
"mappings" : {
"_doc": {
"properties" : {
"order_id": {
"type": "text"
},
"products": {
"type": "nested",
"properties": {
"event_quantity": {
"type": "long"
},
"event_value": {
"type": "float"
},
"product_id": {
"type": "text"
},
"product_lists": {
"type": "text"
},
"product_name": {
"type": "text"
}
}
},
"visitor_id": {
"type": "text"
}
}
}
}
}
PUT visitor_carts/_doc/1
{
"visitor_id" : "VisitorUid_1",
"order_id" : "1000",
"products" : [
{
"product_id" : "10_6",
"product_name" : "product_10_6",
"event_value" : 100.56,
"event_quantity" : 4,
"product_lists" : [
"1"
]
},
{
"product_id" : "10_6",
"product_name" : "product_10_6",
"event_value" : 11,
"event_quantity" : 2,
"product_lists" : [
"42"
]
}
]
}
```
Out of the many purchases that the visitor had made I would like to get the count of documents where the products meet certain criteria:
- Evaluate only products that have "42" in the `product_lists`
- Sum the value of those products by `event_value` and it has to be between for example 100.00 and 200.00 to meet the condition
- Sum the quantity of those products by `event_quantity` and it has to be between 2 and 4 to meet the condition
My instinct tells me that once I make an initial query where I select a specific `visitor_id` then I have to apply a filter on the query results, perhaps with a painless script that would evaluate each product and compare the result with a set of parameters (the `product_lists` value, range for `event_value` and range for `event_quantity` ) but I have been sitting on this for a few days now and cannot get it to work because this operates on the nested documents. All I have managed to do so far is to get counts for all nested documents for a given visitor but that is not even close to what I need.
Please help, perhaps there is a better way of doing that. So far I have reached something like this, but it is a loooong way from home:
```
POST visitor_carts/_search
{
"query" : {
"bool": {
"must": [
{
"term": {
"visitor_id": "VisitorUid_1"
}
},
{
"nested": {
"path": "products",
"query": {
"bool": {
"must": [
{
"term": {
"products.product_lists": "42"
}
}
]
}
}
}
}
]
}
}
}
```
I am using version 6.5
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 25, 2019, 11:56am UTC](https://discuss.elastic.co/t/get-documents-where-sum-of-values-in-filtered-nested-documents-match-a-specified-conditions/187818/2 "2019-07-25T11:56:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.