# Vegalite Pivot with Index data

**URL:** <https://discuss.elastic.co/t/vegalite-pivot-with-index-data/321881>\
**Category:** Kibana\
**Tags:** vega\
**Created:** [December 23, 2022, 6:05am UTC](https://discuss.elastic.co/t/vegalite-pivot-with-index-data/321881 "2022-12-23T06:05:40Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Siva\_Kumar\_Menta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/siva_kumar_menta/32/104159_2.png) [@Siva\_Kumar\_Menta](https://discuss.elastic.co/u/Siva_Kumar_Menta)\
**Post date:** [December 23, 2022, 6:05am UTC](https://discuss.elastic.co/t/vegalite-pivot-with-index-data/321881/1 "2022-12-23T06:05:40Z")

</div>

Hi All,

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

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/9/d/9dcc916eccd17e5081df50e4e54c657968c284ab.png)

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

Index Name: vegalite\_pivot  
Index Data:

```auto
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:

```auto
{
  "$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

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [December 28, 2022, 2:15pm UTC](https://discuss.elastic.co/t/vegalite-pivot-with-index-data/321881/2 "2022-12-28T14:15:58Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/0/9/09d6430ac31ddc880a34e19cf33d5b022477f6e2.png)

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

```auto
{
  $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...

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/7/b7e981cd985ec8c71def1cd91cfd237baa90c8ae.png)

...and continued working from the [Vega editor](https://vega.github.io/editor/) since it gives a more interactive debugging information

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/e/6/e6b5e251e7f2abc689375dde295d0418426ba11c.png)

---

<div class="post-metadata">

**Author:** ![Siva\_Kumar\_Menta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/siva_kumar_menta/32/104159_2.png) [@Siva\_Kumar\_Menta](https://discuss.elastic.co/u/Siva_Kumar_Menta)\
**Post date:** [December 30, 2022, 9:08am UTC](https://discuss.elastic.co/t/vegalite-pivot-with-index-data/321881/3 "2022-12-30T09:08:35Z")

</div>

> [@Siva\_Kumar\_Menta](#):
>
> ```auto
> {
> "$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.

---

<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:** [January 27, 2023, 9:09am UTC](https://discuss.elastic.co/t/vegalite-pivot-with-index-data/321881/4 "2023-01-27T09:09:01Z")

</div>

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