[FIXED] How to group documents by several fields at once?

Issue

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?

Solution

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.

Answered By - Sagar Patel

Answer Checked By - Timothy Miller (PHPFixing Admin)