Query for sum of two nested object fields of a document

[prasad.ram1431]

Hi All,

Could any one help me for a ES query for sum of two nested object fields of a document,
for example,
"_source": {
"Identification": {
"PAYL_HDR": {
"DUNS_NBR": "212003857",
"GEO_REF_ID": "1067",
"GSRL_DT": "2017-08-22T15:24:46.000000",
"INFO_SRC_CD": 20008,
"REC_TYP_CD": 11592
}
}
},
above is the sample data contains in my es document, I need to add INFO_SRC_CD and REC_TYP_CD and show the result in a custom field which will be shown along the all fields of _source.

Please reply me asap.

Thanks & Regards,
Prasad

[dadoonet (David Pilato)]

See the same topic you already opened:

Sum of two fields into a one custom field

As long as it's a script it will work with whatever you want. I'm not telling you how to write the script though. Which I don't know as I'm not using painless. I always prefer modifying my data before indexing it.

[prasad.ram1431]

concept might be same but, i am unable to access the nested object fields, and also i need sample query for doing sum. Please help me if you can.

[dadoonet (David Pilato)]

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.

[LGans316 (Ganesh)]

Is this what you were after?

Request

GET /test/_search?pretty
{
  "query": {
    "match_all": {}
  },
  "script_fields": {
    "summedVal": {
      "script": {
        "lang": "painless",
        "source": "doc['Identification.PAYL_HDR.INFO_SRC_CD'].value + doc['Identification.PAYL_HDR.REC_TYP_CD'].value"
      }
    },
    "_source": {
      "script": "params['_source']"
    }
  }
}

Response

{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 2,
    "max_score": 1,
    "hits": [
      {
        "_index": "test",
        "_type": "test",
        "_id": "aZwPv2IBJc3N3Ppar0XH",
        "_score": 1,
        "fields": {
          "_source": [
            {
              "Identification": {
                "PAYL_HDR": {
                  "DUNS_NBR": "212003858",
                  "GEO_REF_ID": "1068",
                  "GSRL_DT": "2017-08-22T15:24:46.000000",
                  "INFO_SRC_CD": 30008,
                  "REC_TYP_CD": 12592
                }
              }
            }
          ],
          "summedVal": [
            42600
          ]
        }
      },
      {
        "_index": "test",
        "_type": "test",
        "_id": "aJwJv2IBJc3N3Ppa_0X1",
        "_score": 1,
        "fields": {
          "_source": [
            {
              "Identification": {
                "PAYL_HDR": {
                  "DUNS_NBR": "212003857",
                  "GEO_REF_ID": "1067",
                  "GSRL_DT": "2017-08-22T15:24:46.000000",
                  "INFO_SRC_CD": 20008,
                  "REC_TYP_CD": 11592
                }
              }
            }
          ],
          "summedVal": [
            31600
          ]
        }
      }
    ]
  }
}

[prasad.ram1431]

Yes, thank you very much for your response. But when I hit this request, I am getting the below runtime error.

Fielddata is disabled on text fields by default. Set fielddata=true on [Identification.PAYL_HDR.INFO_SRC_CD] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.

Could you please help me on this.

[prasad.ram1431]

Hi Ganesh,

Thank you very much for your reply. I am able to solve my issue. with the below format.

"script_fields": {
"summedVal": {
"script": {
"lang": "painless",
"source": "doc['Identification.PAYL_HDR.INFO_SRC_CD.keyword'].value + doc['Identification.PAYL_HDR.REC_TYP_CD.keyword'].value"
}
}
}

Once again thank you very much for your help.

Thanks & Regards,
Ram Prasad

[LGans316 (Ganesh)]

If it works with keyword, it means the field is most likely defined as a keyword in the mapping. You must check your mapping and consider updating these fields to integer or long as you wanted to sum these 2 fields.

https://www.elastic.co/guide/en/elasticsearch/reference/current/number.html