Search by sum of nested objects

I have list of docs with following struct:

{
  "name": "John Doe",
  "work_experiences": [
    {
      "company": "Accenture",
      "duration": 24
    },
    {
      "company": "IBM",
      "duration": 36
    }
  ]
}
curl -X PUT "localhost:9200/testing?pretty" -H 'Content-Type: application/json' -d'
{
  "mappings": {
    "properties": {
      "name": { "type": "text" },
      "work_experiences": {
        "type": "nested",
        "properties": {
          "company": { "type": "text" },
          "duration": { "type": "integer" }
        }
      }
    }
  }
}
'

curl -X POST "localhost:9200/testing/_doc" -H 'Content-Type: application/json' -d '
{
  "name": "John Doe",
  "work_experiences": [
    {
      "company": "Accenture",
      "duration": 24
    },
    {
      "company": "IBM",
      "duration": 36
    }
  ]
}
'


curl -X POST "localhost:9200/testing/_doc" -H 'Content-Type: application/json' -d '
{
  "name": "Jane Smith",
  "work_experiences": [
    {
      "company": "Accenture",
      "duration": 18
    },
    {
      "company": "Google",
      "duration": 42
    },
    {
      "company": "Accenture",
      "duration": 30
    }
  ]
}
'


I want to list all profiles that were working in total at least 40 months in companies with wildcard match *accent* in company name

In mentioned case Jane Smith was working in total 48 monts in accenture. However I have trouble composing query, I was trying script, aggregations etc. On top of that tere are few other conditions but in general question is about filtering by aggregated sum of nested objects

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