Searching for documents by sum of nested fields attribute

Question

In elasticsearch index I have list of following documents:

{
  "id": 9653,
  "title": "dr",
  "first_name": "John",
  "last_name": "Doe",
  "work_experiences": [
    {
      "company": "Crazy Software",
      "start_date": "2020-12-01",
      "end_date": "2024-04-01",
      "duration": 40,
      "tiers": []
    },
    {
      "company": "Softcore",
      "start_date": "2018-05-01",
      "end_date": "2020-12-01",
      "duration": 31,
      "tiers": []
    },
    ...
  }

Each profile has multiple work experiences. I need to create dynamic query:

List all profiles that:
  were working in total at least 50 months in companies with wildcard match *name* in company name

In this case john doe will be found because he was working 40 monts at crazy SOFTware and 31 months at SOFTcore

Answer 1

Answer #2. Runtime Field Scripting

Your issue can be solved by searching via a runtime field and scripting

GET /durations/_search?filter_path=hits.hits
{
    "runtime_mappings": {
        "total_duration": {
            "type": "long",
            "script": {
                "source": """
                    List workExperiences = params['_source']['work_experiences'];

                    long totalDuration = 0;
                    for (def workExperience : workExperiences) {
                        totalDuration += workExperience.duration;
                    }

                    emit(totalDuration);
                """
            }
        }
    },
    "fields": [
        "total_duration", "id"
    ],
    "_source": false,
    "query": {
        "range": {
            "total_duration": {
                "gte": 50
            }
        }
    }
}

Response

{
    "hits" : {
        "hits" : [
            {
                "_index" : "durations",
                "_type" : "_doc",
                "_id" : "1",
                "_score" : 1.0,
                "fields" : {
                    "total_duration" : [
                        71
                    ],
                    "id" : [
                        9653
                    ]
                }
            }
        ]
    }