Vegalite Pivot with Index data

[Siva_Kumar_Menta]

Hi All,

I have data in Elasticsearch index "vegalite_pivot". While trying to create bar chart with pivot transformation getting below error.

FYI...Provided below index data & vegalite script. Please suggest required changes in the script.

Index Name: vegalite_pivot
Index Data:

POST vegalite_pivot/_bulk
{"index": {}}
{"country": "Norway", "type": "gold", "count": 14}
{"index": {}}
{"country": "Norway", "type": "silver", "count": 14}
{"index": {}}
{"country": "Norway", "type": "bronze", "count": 11}
{"index": {}}
{"country": "Germany", "type": "gold", "count": 14}
{"index": {}}
{"country": "Germany", "type": "silver", "count": 10}
{"index": {}}
{"country": "Germany", "type": "bronze", "count": 7}
{"index": {}}
{"country": "Canada", "type": "gold", "count": 11}
{"index": {}}
{"country": "Canada", "type": "silver", "count": 8}
{"index": {}}
{"country": "Canada", "type": "bronze", "count": 10}

Vegalite Script:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "autosize": {"type": "fit", "contains": "padding"},
  "data": {
    "url": {
      "index": "vegalite_pivot",
      "%context%": true,
      "body": {"size": 4000}
    },
    "format": {"property": "hits.hits"}
  },
  "transform": [
    {
      "pivot": "_source.type",
      "value": "_source.count",
      "groupby": ["_source.country"]
    }
  ],
  "mark": "bar",
  "encoding": {
    "x": {"field": "_source.country", "type": "nominal"},
    "y": {"field": "_source.gold", "type": "quantitative"}
  }
}

Thanks for the help in advance!
Siva Kumar

[jsanz (Jorge Sanz)]

It seems the pivot transform does not like to work with nested objects. If you take the values out of _source it works well:

Here the spec but mind I renamed your index to something keeps my discuss forum indices tidy

{
  $schema: https://vega.github.io/schema/vega-lite/v5.json
  title: Discuss 321881
  data: {
    url: {
      index: discuss-321881
      body: {
        size: 400
      }
    }
    format: {
      property: hits.hits
    }
  }
  "transform": [
    {
      "calculate": "datum._source.country", "as": "country"
    },
    {
      "calculate": "datum._source.type", "as": "type"
    },
    {
      "calculate": "datum._source.count", "as": "count"
    },
    {
      "pivot": "type",
      "value": "count",
      "groupby": ["country"]
    }
  ]
  "mark": "bar",
  "encoding": {
    "x": {"field": "country", "type": "nominal"},
    "y": {"field": "gold", "type": "quantitative"}
  }
}

Finally to get to a solution (in case it helps for future debugging or anyone else reading this) I started from your code but using the Inspect tool I got the equivalent vega spec...

...and continued working from the Vega editor since it gives a more interactive debugging information

[Siva_Kumar_Menta]

@Siva_Kumar_Menta

{
  "$schema": "https://vega.github.io/schema/vega-lite/v4.json",
  "autosize": {"type": "fit", "contains": "padding"},
  "data": {
    "url": {
      "index": "vegalite_pivot",
      "%context%": true,
      "body": {"size": 4000}
    },
    "format": {"property": "hits.hits"}
  },
  "transform": [
    {
      "pivot": "_source.type",
      "value": "_source.count",
      "groupby": ["_source.country"]
    }
  ],
  "mark": "bar",
  "encoding": {
    "x": {"field": "_source.country", "type": "nominal"},
    "y": {"field": "_source.gold", "type": "quantitative"}
  }
}

With your inputs, my problem is solved. Thanks alot!!!
Sorry for the delayed response.