# Group buckets by another fields keyword

**URL:** <https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940>\
**Category:** Kibana\
**Created:** [January 22, 2021, 4:42pm UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940 "2021-01-22T16:42:05Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![grra](https://avatars.discourse-cdn.com/v4/letter/g/a6a055/32.png) [@grra](https://discuss.elastic.co/u/grra)\
**Post date:** [January 22, 2021, 4:42pm UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940/1 "2021-01-22T16:42:06Z")

</div>

Hi,  
I want to visualize a **Bar Chart** with **Vega**.

1. I have Houses and Rooms. (Rooms can have different colors)  
Each `house`(1, 2, 3, ...) has several rooms (1, 2, 3, ...).  
The `house` and `room` fields are from type `number`.

2. Each `house` has a number of corresponding Rooms. And every `room` belongs to one house.

3. On the **X-Axis** I want to show the houses in an ascending manner.  
And on the **Y-Axis** I need the amount of `rooms` which belong to each `house`.

4. Also I would like to split the y Axis by the **Rooms Colors**.

I am stuck at point 2.  
I can not find the correct Elastic Search Query to get `house` and related `rooms` in my Vega data.

Is it even possible to visualize my use case?

---

<div class="post-metadata">

**Author:** ![matw](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matw/32/13913_2.png) [@matw](https://discuss.elastic.co/u/matw)\
**Post date:** [January 28, 2021, 10:23am UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940/2 "2021-01-28T10:23:25Z")

</div>

Hi

Could you share you're current data structure, and Vega configuration?

Many thx!  
Best,  
Matthias

---

<div class="post-metadata">

**Author:** ![grra](https://avatars.discourse-cdn.com/v4/letter/g/a6a055/32.png) [@grra](https://discuss.elastic.co/u/grra)\
**Post date:** [January 29, 2021, 12:04pm UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940/3 "2021-01-29T12:04:47Z")

</div>

Thanks for reply,  
sure I can try to sum it up a bit.

**This is my response of JSON query in Kibana Dev Tools:**

```
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "houses" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 459508,
      "buckets" : [
        {
          "key" : 1122,
          "doc_count" : 41,
          "room" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : 742,
                "doc_count" : 41,
                "colors" : {
                  "doc_count_error_upper_bound" : 0,
                  "sum_other_doc_count" : 0,
                  "buckets" : [
                    {
                      "key" : "green",
                      "doc_count" : 24
                    },
                    {
                      "key" : "red",
                      "doc_count" : 17
                    }
                  ]
                }
              }
            ]
          }
        }, ... ... ... # and so on

```

**This is my Vega configuration right now:**

```
{
  "$schema": "https://vega.github.io/schema/vega/v2.json",
  "description": "",

  "title": "Total Overview Of Houses Room Colors",
  "padding": 10,
  "data": {
    "name": "total_overview_house",
    "url": {
      "index": "indexname",
      "body": {
      
        # #####################################################
        # ####################### json query ##################
        # #####################################################          
}
    },

    "format": {
      "property": "aggregations.houses.buckets"
    }
  },
  

  "scales":[
    {
      "name": "x",
      "type": "band",
      "domain" : { "data": "total_overview_house","field": "key" },
      "range": "width",
      "padding": 0.05,
      "round": true
    },
    {
      "name":"y",
      "type": "linear",
      "nice": true, "zero": true,
      "domain":{ "data": "total_overview_house","field": "doc_count" },
      "range":"height"
    }
  ],
  
  
  "axes": [
    { "orient": "bottom", "scale": "x" },
    { "orient": "left", "scale": "y" },
  ],
  
  
  "marks": [
    {
      "type": "group",
      "from": { "data":"total_overview_house" },
      "encode": {
        "enter": {
          "x": { "scale": "x", "field": "key" },
          "width": { "scale": "x", "band": 1 },
          "y": { "scale": "y", "field": "doc_count" },
          "y2": { "scale": "y", "value": 0 }
        },
        "update": {
          "fill": { "value": "lightblue" }
        },
        "hover": {
          "fill": { "value": "orange" }
        }
      }
    },
    {
      "type": "text",
      "encode": {
        "enter": {
          "align": { "value": "center" },
          "baseline": { "value": "bottom" },
          "fill": {"value": "#333" }
        },
        "update": {
          "x": { "scale": "x", "signal": "tooltip.key", "band": 0.5 },
          "y": { "scale": "y", "signal": "tooltip.doc_count", "offset": -2 },
          "text": { "signal": "tooltip.doc_count" },
          "fillOpacity": [
            { "test": "datum === tooltip", "value": 0 },
            { "value": 1 }
          ]
        }
      }
    }
  ],
  "signals": [
    {
      "name": "tooltip",
      "value": { },
      "on": [
        { "events": "rect:mouseover", "update": "datum" },
        { "events": "rect:mouseout", "update": "{}" }
      ]
    }
  ]
}

```

**In my Visualization I can see:**

x-Axis: House Numbers  
y-Axis: House Numbers Count.

**What I actually try to achieve:**

x-Axis: House Numbers  
y-Axis: The Rooms with the count of each Color (of the houses) --\> See Json Response

In the Docu of Vega I can not find the correct way to do that.

Thanks in advance.

---

<div class="post-metadata">

**Author:** ![matw](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matw/32/13913_2.png) [@matw](https://discuss.elastic.co/u/matw)\
**Post date:** [February 1, 2021, 8:50pm UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940/4 "2021-02-01T20:50:31Z")

</div>

Should the result be a stacked bar chart like this?

> **[Stacked Bar Chart](https://vega.github.io/vega-lite/examples/stacked_bar_weather.html)**
>
> Vega-Lite - a high-level grammar for statistical graphics. Vega-Lite provides a higher-level grammar for visual analysis, comparable to ggplot or Tableau, that generates complete Vega specifications. Vega-Lite specifications consist of simple...

---

<div class="post-metadata">

**Author:** ![grra](https://avatars.discourse-cdn.com/v4/letter/g/a6a055/32.png) [@grra](https://discuss.elastic.co/u/grra)\
**Post date:** [February 1, 2021, 9:34pm UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940/5 "2021-02-01T21:34:24Z")

</div>

Yes that's right, but actually in **Vega** , and the "data" is fetched by a query.  
The problem is that I can not reach the data. Everything inside `buckets.room` is **not defined**.

Because I read that Vega has no good solution for visualize subqueries, I tried it with a " **Composite Aggregation**" query and got the following **data** :

```auto
  "aggregations" : {
    "house_rooms" : {
      "after_key" : {
        "house_number" : 123,
        "room_number" : 456,
        "color" : "red"
      },
      "buckets" : [
        {
          "key" : {
            "house_number" : 1124,
            "room_number" : 344,
            "color" : "green"
          },
          "doc_count" : 3
        },
        {
          "key" : {
            "house_number" : 1123,
            "room_number" : 744,
            "color" : "red"
          },
          "doc_count" : 17
        }, ...

```

Is it possible to get those in a stacked bar Chart?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 1, 2021, 9:34pm UTC](https://discuss.elastic.co/t/group-buckets-by-another-fields-keyword/261940/6 "2021-03-01T21:34:51Z")

</div>

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