How to group documents by several fields at once?

Devid Mercer 0
Viewed 16

I have a simple search using scripts:

{
 "query": {
  "bool": {
   "filter": [
    {
     "exists": {
      "field": "fieldX"
     }
    },
    {
     "exists": {
      "field": "fieldY"
     }
    }
   ]
  }
 },
 "aggs": {
  "groupBy": {
   "terms": {
    "script": {
     "source": "doc['fieldX.keyword'].value+','+doc['fieldY.keyword'].value"
    }
   }
  }
 }
}

Groups are the result of this search:

"buckets" : [
        {
          "key" : "valueX1,valueY1",
          "doc_count" : 165
        },
        {
          "key" : "valueX2,valueY2",
          "doc_count" : 45
        }
]

The main problem here is: If all the fields (fieldX and fieldY) in documents exist - everything will be fine. If at least one field is missing, the search will return nothing.

I tried to rewrite this to normal search using aggs, terms, field but unsuccessfully.

Any idea how I can rewrite this search to keep the original result but avoid the described problem?

elasticsearch
1 Answers

You need to update your script and add some null pointer check as shown below:

{
  "aggs": {
    "groupBy": {
      "terms": {
        "script": {
          "source": """
          if(doc['fieldX.keyword'].size()!=0 &&doc['fieldY.keyword'].size()!=0){
            doc['fieldX.keyword'].value+','+doc['fieldY.keyword'].value
          }else if(doc['fieldX.keyword'].size()==0 && doc['fieldY.keyword'].size()!=0){
            doc['fieldY.keyword'].value
          }else if(doc['fieldY.keyword'].size()==0 && doc['fieldX.keyword'].size()!=0){
            doc['fieldX.keyword'].value
          }
          
          """
        }
      }
    }
  }
}

You can remove else if part if you want to generate aggregation only when both fields value is available. Also, exists query you can remove after updating script.

Sagar Patel0
Related