# Iterate over array with matched params query DSL aggregations **URL:** \ **Category:** Elasticsearch\ **Created:** [February 28, 2019, 9:55am UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307 "2019-02-28T09:55:29Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![stephane317](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephane317/32/41393_2.png) [@stephane317](https://discuss.elastic.co/u/stephane317)\ **Post date:** [February 28, 2019, 9:55am UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307/1 "2019-02-28T09:55:29Z")
ES Version: 6.2.2 I'm trying to make a dashboard filtered by office, and get for a specific office (the selected one i get in the my http request) the price associate to this office. Here is my ES mapping ``` "budgets": { "properties": { "office": { "properties": { "_id": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } }, "name": { "type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } } }, "priceHT": { "type": "long" }, "priceTTC": { "type": "long" } } } ``` Here is my painless script multiline: I replace the params.officeId by the selected one i get from the client ``` double total = 0.0; if(params.officeId !== 0){ if(doc.containsKey('budgets')){ for (p in params['_source'].budgets) { if(p.office._id == params.officeId) { total += p.priceTTC } if(p.office._id !== params.officeId) { total += p.priceTTC } } } if(!doc.containsKey('budgets')){ total += 9999999 } } if(params.officeId == 0) { if(doc['priceTTC'] !== null) { total += doc['priceTTC'].value } if(doc['priceTTC'] == null) { total += 0 } } return total" ``` Here is the full query which is working fine expect when i have a params.officeId ``` { "size": 0, "query": { "bool": { "must": [], "filter": { "bool": { "must": [], "should": [] } } } }, 'aggs': { "supplier": { "filter": { "bool": { "must": [], "should": [] } }, "aggregations": { "filtered_supplier": { "terms": { "field": "supplier._id.keyword", "size": 1000, }, "aggs": { "salesTTC": { "sum": { "script": { "lang": "painless", "source": "double total = 0.0;if(params.officeId !== 0){ if(doc.containsKey('budgets')){ for (p in params['_source'].budgets) { if(p.office._id == params.officeId) { total += p.priceTTC } if(p.office._id !== params.officeId) { total += p.priceTTC } } if(!doc.containsKey('budgets')){total += 1}}} if(params.officeId == 0) { if(doc['priceTTC'] !== null) { total += doc['priceTTC'].value } if(doc['priceTTC'] == null) {total += 0} } return total;", "params": { "officeId": 0 } } } }, "salesHT": { "sum": { "script": { "lang": "painless", "source": "double total = 0.0;if(params.officeId !== 0){ if(doc.containsKey('budgets')){ for (p in params['_source'].budgets) { if(p.office._id == params.officeId) { total += p.priceHT } if(p.office._id !== params.officeId) { total += p.priceHT } } if(!doc.containsKey('budgets')){total += 1}}} if(params.officeId == 0) { if(doc['priceHT'] !== null) { total += doc['priceHT'].value } if(doc['priceHT'] == null) {total += 0} } return total;", "params": { "officeId": 0 } } } } } } } } } }; ``` I'm struggling since 2 days and tried many thinks, help me please 🙂 EDIT: the execution is not going in the if(doc.containsKey('budgets')){...} and always get 9999999 the test value i put in the line += 9999999 Thanks ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [February 28, 2019, 10:23am UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307/2 "2019-02-28T10:23:49Z")
Please don't post images of text as they are hardly readable and not searchable. Instead paste the text and format it with `` icon. Check the preview window. Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible. A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer. ---
**Author:** ![stephane317](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephane317/32/41393_2.png) [@stephane317](https://discuss.elastic.co/u/stephane317)\ **Post date:** [February 28, 2019, 10:39am UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307/3 "2019-02-28T10:39:51Z")
Thanks for your reply, here we go, i just past the whole thing like your ask as simple as i can 🙂 EDIT: Better edit done ---
**Author:** ![stephane317](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephane317/32/41393_2.png) [@stephane317](https://discuss.elastic.co/u/stephane317)\ **Post date:** [February 28, 2019, 11:13am UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307/4 "2019-02-28T11:13:09Z")
I just solved my problem because I took a step back on my code thanks to @dadoonet The problem was that despite finding the good officeId, sometimes the priceTTC or priceHT was not defined ... i added an if to check if the value was there and the whole just work PS: If anyone saw this and have a suggestion on my query don't hesitate i'm new on elasticsearch, it's will be full appreciate 🙂 Great day ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [February 28, 2019, 1:28pm UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307/5 "2019-02-28T13:28:57Z")
The only suggestion I have is to compute the most as you can at index time and not at query time. I'd compute `salesTTC` and `salesHT` at index time. This can happen in your application layer (better IMO) or using a Painless Script Processor in an ingest Pipeline. Then computing the aggregation would take less time specifically if you have a lot matches. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 28, 2019, 1:28pm UTC](https://discuss.elastic.co/t/iterate-over-array-with-matched-params-query-dsl-aggregations/170307/6 "2019-03-28T13:28:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.