Group buckets by another fields keyword

[grra (Ralf)]

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?

[matw (Matthias Wilhelm)]

Hi

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

Many thx!
Best,
Matthias

[grra (Ralf)]

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.

[matw (Matthias Wilhelm)]

Should the result be a stacked bar chart like this?

Stacked Bar Chart

[grra (Ralf)]

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:

  "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?