Aggregate across the document from Nested Documents

Elastic Stack Elasticsearch
1

Hello I have a document like this

Example Document 1

  "applicationId": "12345",
  "accountId": "test",
  "interactionInfo": [
    {
      "title": "body",
      "value": "test"
    },
    {
      "title": "UserLocation",
      "value": "US"
    },
  {
      "title": "CustomerLocation",
      "value": "IN"
    }
    {
      "title": "price",
      "value": "0.26"
    }
  ]
}

Example Document 2

{
  "applicationId": "12345",
  "accountId": "test",
  "interactionInfo": [
    {
      "title": "body",
      "value": "hello"
    },
    {
      "title": "UserLocation",
      "value": "GB"
    },
   {
      "title": "CustomerLocation",
      "value": "IN"
    }
    {
      "title": "price",
      "value": "0.45"
    }
  ]
}

Example Document 3

{
  "applicationId": "12345",
  "accountId": "test",
  "interactionInfo": [
    {
      "title": "body",
      "value": "hello"
    },
    {
      "title": "UserLocation",
      "value": "US"
    },
    {
      "title": "CustomerLocation",
      "value": "IN"
    }
    {
      "title": "price",
      "value": "0.30"
    }
  ]
}

where interaction info is the nested documents - I want to do an aggregation on the title userLocation and to calculate the total price based on the userLocation

Below is the aggregation query that i performed only gives me the list of countries presented in my document and i was stuck calculate the Sum of price for the particular user Location

"aggs": {
    "accountId": {
      "terms": {
        "field": "accountId",
        "size": 10
      },"aggs": {
        "Nested Aggregation": {
          "nested": {
            "path": "interactionInfo"
          },"aggs": {
            "Filter Based on Customer Location": {
              "filter": {
                "term": {
                  "interactionInfo.title.keyword": "UserLocation"
                }
              },"aggs": {
                "Country Wise": {
                  "terms": {
                    "field": "interactionInfo.value.keyword",
                    "size": 150
                  }
                }
              }
            }
          }
        }
      }
    }
  }

The above aggregation query will give me following result

        {
          "key": "test",
          "Nested Aggregation": {
            "Filter Based on Customer Location": {
              "Country Wise": {
                "buckets": [
                  {
                    "key": "US",
                    "doc_count": 2
                  },
                  {
                    "key": "GB",
                    "doc_count": 1
                  }
                ]
              }
            }
          }
        }

What I am Expecting is was to calculate Sum of all price based on the location. Is it possible to aggregate two different document in nested document at the same time.If it is possible means then how so.

{
  "Key" : "Test",
  "Bukets" : [
    {
      "Key" : "US",
       "doc_count" : "2",
       "Sum of Price" : {
          "sum" : "0.56"
       }
    },
    {
      "Key" : "GB",
      "doc_count" : "1",
      "Sum of Price" : {
          "sum" : "0.30"
       }
    }
    ]
}

Above is the result i am looking for.

2

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