Create Bar chart for array of nested objects

[masoud_safa]

Hi
i have problem in drawing chart for my data with vega
my data is like this

{
	"took": 10,
	"timed_out": false,
	"hits": {
		"hits": [
			{
				"_index": "request_index",
				"_id": "eaae227e-cf8f-411d-a8b3-0d66595d8730",
				"_source": {
					"creationDate": "2021-12-04T13:03:13.186Z",
					"requests": [
						{
							"searchEngine": "Google",
							"execTime": 235
						},
						{
							"searchEngine": "Bing",
							"execTime": 952
						}
					]
				}
			},
			{
				"_index": "request_index",
				"_id": "ac03a6de-23af-4e2d-9315-a36a82ffd251",
				"_source": {
					"creationDate": "2021-11-04T13:03:13.186Z",
					"requests": [
						{
							"searchEngine": "Yahoo",
							"execTime": 75
						},
						{
							"searchEngine": "Google",
							"execTime": 145
						},
						{
							"searchEngine": "Bing",
							"execTime": 170
						}
					]
				}
			}
		]
	}
}

I want to draw bar chart for average of execTime per searchEngine like this

and also historical average for execTime and split charts by searchEngine

can anyone help me?

[rashmi (kulkarni)]

copying @Marco_Liberati for some insights on this. Please help when you have time.

Thanks
Rashmi

[aaron-nimocks]

See in Vega Editor. If you need explanation on how the data was transformed let me know @masoud_safa

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "width": 400,
  "height": 200,
  "padding": 5,
  "data": [
    {
      "name": "table",
      "values": {
        "took": 10,
        "timed_out": false,
        "hits": {
          "hits": [
            {
              "_index": "request_index",
              "_id": "eaae227e-cf8f-411d-a8b3-0d66595d8730",
              "_source": {
                "creationDate": "2021-12-04T13:03:13.186Z",
                "requests": [
                  {"searchEngine": "Google", "execTime": 235},
                  {"searchEngine": "Bing", "execTime": 952}
                ]
              }
            },
            {
              "_index": "request_index",
              "_id": "ac03a6de-23af-4e2d-9315-a36a82ffd251",
              "_source": {
                "creationDate": "2021-11-04T13:03:13.186Z",
                "requests": [
                  {"searchEngine": "Yahoo", "execTime": 75},
                  {"searchEngine": "Google", "execTime": 145},
                  {"searchEngine": "Bing", "execTime": 170}
                ]
              }
            }
          ]
        }
      },
      "format": {"property": "hits.hits"},
      "transform": [
        {"type": "flatten", "fields": ["_source.requests"], "as": ["requests"]},
        {
          "type": "formula",
          "as": "searchEngine",
          "expr": "datum['requests']['searchEngine']"
        },
        {
          "type": "formula",
          "as": "execTime",
          "expr": "datum['requests']['execTime']"
        },
        {
          "type": "aggregate",
          "groupby": ["searchEngine"],
          "fields": ["execTime"],
          "ops": ["average"],
          "as": ["execTime"]
        }
      ]
    }
  ],
  "scales": [
    {
      "name": "xscale",
      "type": "band",
      "domain": {"data": "table", "field": "searchEngine"},
      "range": "width",
      "padding": 0.05,
      "round": true
    },
    {
      "name": "yscale",
      "domain": {"data": "table", "field": "execTime"},
      "nice": true,
      "range": "height"
    }
  ],
  "axes": [
    {"orient": "bottom", "scale": "xscale"},
    {"orient": "left", "scale": "yscale"}
  ],
  "marks": [
    {
      "type": "rect",
      "from": {"data": "table"},
      "encode": {
        "enter": {
          "x": {"scale": "xscale", "field": "searchEngine"},
          "width": {"scale": "xscale", "band": 1},
          "y": {"scale": "yscale", "field": "execTime"},
          "y2": {"scale": "yscale", "value": 0}
        },
        "update": {"fill": {"value": "steelblue"}},
        "hover": {"fill": {"value": "red"}}
      }
    }
  ]
}