Output aggregation results using visualization in Kibana


(Phantom) #1

I have such a structure in index answers.

{"can_view": ["F1", "F2", "F3"],"ext_id":20,"alias":"firstname","value":"John"}
{"can_view": ["F1", "F2", "F3"],"ext_id":20,"alias":"lastname","value":"Mullen"}
{"can_view": ["F1", "F2", "F3"],"ext_id":20,"alias":"phone","value":"99877"}
{"can_view": ["F1", "F2"],"ext_id":1,"alias":"firstname","value":"Janelle"}
{"can_view": ["F1"],"ext_id":2,"alias":"phone","value":"222555"}

I can make such a request.

GET /answers/_search?pretty
{
    "size": 0,
    "aggs" : {
        "alias_group" : {
            "terms" : { "field" : "ext_id"},
            "aggs": {
                "bucket_items": {
                    "top_hits": {
                        "_source": {
                            "includes": [ "ext_id", "alias", "value", "can_view" ]
                        },
                        "size": 100
                    }
                }
            }
        }
    }
}

And I get such a result:

{
  "took" : 7,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 5,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "alias_group" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 20,
          "doc_count" : 3,
          "bucket_items" : {
            "hits" : {
              "total" : 3,
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "answers",
                  "_type" : "_doc",
                  "_id" : "2",
                  "_score" : 1.0,
                  "_source" : {
                    "can_view" : [
                      "F1",
                      "F2",
                      "F3"
                    ],
                    "ext_id" : 20,
                    "alias" : "lastname",
                    "value" : "Mullen"
                  }
                },
                {
                  "_index" : "answers",
                  "_type" : "_doc",
                  "_id" : "1",
                  "_score" : 1.0,
                  "_source" : {
                    "can_view" : [
                      "F1",
                      "F2",
                      "F3"
                    ],
                    "ext_id" : 20,
                    "alias" : "firstname",
                    "value" : "John"
                  }
                },
                {
                  "_index" : "answers",
                  "_type" : "_doc",
                  "_id" : "3",
                  "_score" : 1.0,
                  "_source" : {
                    "can_view" : [
                      "F1",
                      "F2",
                      "F3"
                    ],
                    "ext_id" : 20,
                    "alias" : "phone",
                    "value" : "99877"
                  }
                }
              ]
            }
          }
        },
        {
          "key" : 1,
          "doc_count" : 1,
          "bucket_items" : {
            "hits" : {
              "total" : 1,
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "answers",
                  "_type" : "_doc",
                  "_id" : "4",
                  "_score" : 1.0,
                  "_source" : {
                    "can_view" : [
                      "F1",
                      "F2"
                    ],
                    "ext_id" : 1,
                    "alias" : "firstname",
                    "value" : "Janelle"
                  }
                }
              ]
            }
          }
        },
        {
          "key" : 2,
          "doc_count" : 1,
          "bucket_items" : {
            "hits" : {
              "total" : 1,
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "answers",
                  "_type" : "_doc",
                  "_id" : "5",
                  "_score" : 1.0,
                  "_source" : {
                    "can_view" : [
                      "F1"
                    ],
                    "ext_id" : 2,
                    "alias" : "phone",
                    "value" : "222555"
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}

So I get several buckets for each ext_id.
Now I want to make something similar in a DataTable using Kibana visualization.
So I add such a metric:

Metrics:
Metric:
Aggregation: Top Hit
Field: value.keyword
Aggregate with: Concatenate
Size: 25
Sort On: _id
Order: Ascending

Buckets:
Split Rows:
Aggregation: Terms
Field: ext_id
Order By: Alphabetical
Order: Ascending
Size: 50

Split Rows:
Aggregation: Terms
Field: alias.keyword
Order By: Alphabetical
Order: Ascending
Size: 50

I get a result:
[See screenshot]

This is not exactly what I want to get.
I want to get such an output:

ext_id.....firstname.....lastname.....phone.
1..........Janelle.......--------......---..
2..........---------.....--------.....222555
20.........John..........Mullen.......99877

Is there a way to make such a visualization in kibana? What metricks/buckets/filters should I use?
Thanks.

Versions that I use are:

elasticsearch 6.5.1
kibana 6.5.1
X-Pack