Elasticsearch query to get values of multiple attributes

Question

I have below json data:

{
  "_index": "logs",
  "_type": "_doc",
  "_id": "122",
  "_version": 7,
  "_score": null,
  "_source": {
    "Data": {
      "DiskTotal": 62701268992,
      "DiskFree": 56609468416,
      "DiskStatus": "Normal",
      "Version": "2.0",
      "Ip": "192.168.0.106"
    },
    "Created": "2021-01-04T14:13:48.245760",
    "Device": "T1"
    "Customer": "demo1"
    
  },
  "fields": {
    "Data.UpTime": [
      "2021-01-04T14:10:05.000Z"
    ],
    "Created": [
      "2021-01-04T14:13:48.245Z"
    ]
  },
  "sort": [
    1609769628245
  ]
}

I have below query:

{
  "aggs": 
  {
    "device_name": {
      "terms": {
        "field": "Device.keyword"
        
      }
    }
  }
}

Now this gives me below reponse:

{
  "took" : 6,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 325,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "device_name" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 16,
      "buckets" : [
        {
          "key" : "T8",
          "doc_count" : 74
        },
        {
          "key" : "T6",
          "doc_count" : 45
        },
        {
          "key" : "T4",
          "doc_count" : 44
        }
      ]
    }
  }
}

This is giving me all the device names I have in Elasticsearch but along with device name I also want to get which customer it belongs to, so something like below:

"buckets" : [
        {
          "key1" : "T8",
          "key2" : "Demo1",
          "doc_count" : 74
        },
        {
          "key1" : "T6",
          "key2" : "Demo1",
          "doc_count" : 45
        },
        {
          "key1" : "T4",
          "key2" : "Demo2",
          "doc_count" : 44
        }
      ]

How can I modify the query to include customer name as well with device name?

Answer 1

You need to use terms aggregation along with top hits aggregation, to achieve your use case.

Adding a working example

Index Data:

{
  "Data": {
    "DiskTotal": 62701268992,
    "DiskFree": 56609468416,
    "DiskStatus": "Normal",
    "Version": "2.0",
    "Ip": "192.168.0.106"
  },
  "Created": "2021-01-04T14:13:48.245760",
  "Device": "T1",
  "Customer": "demo1"
}

Search Query:

{
  "aggs": {
    "device_name": {
      "terms": {
        "field": "Device.keyword"
      },
      "aggs": {
        "top_faq_hits": {
          "top_hits": {
            "_source": {
              "includes": [
                "Customer"
              ]
            },
            "size": 1
          }
        }
      }
    }
  }
}

Search Result:

"aggregations": {
    "device_name": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "T1",          // note this
          "doc_count": 1,
          "top_faq_hits": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": 1.0,
              "hits": [
                {
                  "_index": "65567027",
                  "_type": "_doc",
                  "_id": "1",
                  "_score": 1.0,
                  "_source": {
                    "Customer": "demo1"   // note this
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }