Iterate over array with matched params query DSL aggregations

Elastic Stack Elasticsearch
1

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 :slight_smile:

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

2

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. 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.

3

Thanks for your reply, here we go, i just past the whole thing like your ask as simple as i can :slight_smile:

EDIT: Better edit done

4

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 :slight_smile:
Great day

5

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.

1 Like
6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.