Aggregating on a nested field

I have an elastic search schema as below.

{
  "mappings": {
    "properties": {
      "process_id": {
        "type": "keyword"
      },
      "user_info": {
        "type": "nested",
        "properties": {
          "first_name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "last_name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "place": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
      

2. I have added the below documents to the index.

POST processes/_bulk 
  {"index":{"_id":1}}
{"process_id": "123", "user_info": [{"first_name": "A", "last_name": "B","place":"London"},{"first_name": "C", "last_name": "D","place":"Moscow"}]}
{"index":{"_id":2}}
{"process_id": "123", "user_info": [{"first_name": "C", "last_name": "B","Place":"Delhi"},{"first_name": "A", "last_name": "D","Place":"Bangalore"}]}

3. I wanted to search for process_id as 123, first_name as A and last_name as B, and and aggregate on Place of the user_info. I am trying to do it as below.

{   "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "process_id:123"
          }
        },
        {
          "nested": {
            "path": "user_info",
            "query": {
              "query_string": {
                "query": "user_info.first_name:A AND user_info.last_name:B"
              }
            }
          }
        }
      ]
    }   },   "aggs": {
    "user_info": {
      "nested": {
        "path": "user_info"
      },
      "aggs": {
        "user_info.place": {
          "terms": {
            "field": "user_info.place"
          }
        }
      }
    }   } }

The result returns the first document correctly. However, the aggregate on the field place returns both London and Moscow. However, I want London to be returned as the output. I am not sure how to proceed with this. I have tried Nested Filter Search Aggregation as well but no success on the same.

Hello,
You need to apply filter aggregation on nested level to sweep Moscow out.

1 Like

Can you please elaborate a bit more?

Pls check

thank you! It worked

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