# Using Elastic SQL with Vega

**URL:** <https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455>\
**Category:** Kibana\
**Tags:** vega\
**Created:** [April 30, 2020, 1:28am UTC](https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455 "2020-04-30T01:28:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mlehotsky](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mlehotsky/32/31436_2.png) [@mlehotsky](https://discuss.elastic.co/u/mlehotsky)\
**Post date:** [April 30, 2020, 1:28am UTC](https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455/1 "2020-04-30T01:28:51Z")

</div>

Hi Kibana team,

I have this Elastic SQL query which works as expected:

```
POST /_sql?format=txt
{
    "query": "SELECT id AS Images, (MAX(pull_count)-MIN(pull_count)) AS Pulls_Added FROM \"project1\" GROUP BY id"
}

```

Result:

```
Images | Pulls_Added  
-------------------------------
image_1 |16.0       
image_2 |27.0       
image_3 |206.0       
image_4 |38.0        

```

I need to visualize this result in Kibana dashboard and I learned that Vega may be way to go because it supports Elastic SQL (not sure if Vega is the only visualization which supports it). Problem is that I can't find any example of using Elastic SQL with Vega visualization and can't figure out the syntax.  
Please help me.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [May 1, 2020, 9:57am UTC](https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455/2 "2020-05-01T09:57:20Z")

</div>

Vega doesn't support ES SQL at the moment. This is the issue to track progress on that task: [https://github.com/elastic/kibana/issues/62333](https://github.com/elastic/kibana/issues/62333)

For Vega, you would have to switch to regular Elasticsearch aggregation queries.

If you want to leverage ESSQL, you can use Canvas. It's not possible to put those on a dashboard yet, but Canvas offers a lot of tools to build a nice "dashboard" there.

---

<div class="post-metadata">

**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\
**Post date:** [May 1, 2020, 11:12am UTC](https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455/3 "2020-05-01T11:12:26Z")

</div>

Just to complement @flash1293 answer, you could still use Vega but you have to convert your query to Elasticsearch DSL:

```auto
GET project1/_search
{
  "size": 0,
  "aggs": {
    "Images": {
      "terms": {
        "field": "id",
        "size": 100
      },
      "aggs": {
        "pull_count_stats": {
          "stats": {
            "field": "pull_count"
          }
        },
        "Pulls_Added": {
          "bucket_script": {
            "buckets_path": {
              "max": "pull_count_stats.max",
              "min": "pull_count_stats.min"
            },
            "script": "return params.max - params.min"
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![mlehotsky](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mlehotsky/32/31436_2.png) [@mlehotsky](https://discuss.elastic.co/u/mlehotsky)\
**Post date:** [May 1, 2020, 11:29pm UTC](https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455/4 "2020-05-01T23:29:06Z")

</div>

Thank you @Luca_Belluccini, this is exactly what I was looking for! After adding this query to my Vega visualization i got desired result! I wasn't aware that this is achievable without using ESSQL.

---

<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:** [May 29, 2020, 11:29pm UTC](https://discuss.elastic.co/t/using-elastic-sql-with-vega/230455/5 "2020-05-29T23:29:17Z")

</div>

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