Help for painless iterate nested fields

[Sky_Iprd]

This is my query, I have try few of them ( below ) trying to iterate the nested fields for compare the profit between 2017 and 2018 year. How can I iterate the nested fields ? Please help in advance...

1.for (int i=0; i<params["_source"].profit_nested; ++i)
-null pointer
2. for(item in params._source.profit_nested)
-null pointer
3. for (int i =0; i < doc['profit_nested'].value.length; i++)
-No field found for [profit_nested] in mapping with types

{
  "size": 1,
  "query": {
"bool": {
  "must": {
    "range" : {
      "date" : {
        "gte":"2017-12","lte":"2018-01","format":"yyyy-MM"
      }
    }
  },
  "filter": {
    "nested": {
      "path": "profit_nested",
        "query": {
          "script": {
              "script": """
              int year2018;
              int year2017;
              int year2016;                  
              for (int i=1; i< params["_source"].profit_nested.year; i++){
                year2018 = i;
              }
              if(year2017 == year2018)
              { return(true) } else { return(false) }
              """
        }
      }
    }
  }
}
 }
}

This is my data result

"hits" : {
"total" : 1,
"max_score" : 1.0,
"hits" : [
  {
    "_index" : "ftesting_nested",
    "_type" : "testing",
    "_id" : "dPWn92cBPjdQI7ZWakad",
    "_score" : 1.0,
    "_source" : {
      "item" : "shirt",
      "color": "red",
      "date" : "2018-12",
      "profit_nested" : [
        {
          "year" : "2014",
          "profit" : 1000
        },
        {
          "year" : "2015",
          "profit" : -500
        },
        {
          "year" : "2016",
          "profit" : 1000
        },
        {
          "year" : "2017",
          "profit" : 2000
        },
        {
          "year" : "2018",
          "profit" : 3000
        }
      ]
    }
  }

[erwesc]

have you figured this out? i have a similar requirement. thanks

[Sky_Iprd]

not yet... still trying

[abdon (Abdon Pijpelink)]

This approach is not going to work. You can not access the values of all nested objects in a script at query time. Your script query only works on one nested object at a time.

My advice would be to add the information you want to query for when you index your documents. Either in the application that writes the data to Elasticsearch, or by using an ingest pipeline. The ingest pipeline could for example use a script processor for that. At index time you do have access to the full _source of your document, including all nested objects.

If you really need the flexibility of script queries (for example because you do not know what it is that you want to potentially want to query for), then one approach would be to move away from nested types and simply using the year as the field name. Your documents could be indexed like this:

{
  "2014": 1000,
  "2015": -500,
  "2016": 1000,
  "2017": 2000,
  "2018": 3000
}

Now you can use a script that has access to all of the values that you could be interested in. This is not something you would do if it would lead to a lot of fields in your mappings, but as you're probably not going to have thousands of different years in your dataset, this approach will work.