# SQL Structure to JSON Structure the ElasticSearch?

**URL:** <https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037>\
**Category:** Elasticsearch\
**Created:** [December 16, 2019, 4:44pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037 "2019-12-16T16:44:22Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 16, 2019, 4:44pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/1 "2019-12-16T16:44:22Z")

</div>

Hi guys, I would like to ask a question. I want to convert the mysql SQL structure to the JSON structure for elasticSearch. that is, the parameters to limit, sort, group by, select and where then you fall into the configuration of which are the operators for the conditions of AND = Must and OR should but what would be the complete list?

**Example**  
**AND = Must and OR should**

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [December 18, 2019, 10:47am UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/2 "2019-12-18T10:47:11Z")

</div>

I think it would be helpful to use the[` /_sql/translate`](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html) endpoint and see how your SQL queries can be translated to ES search and aggregation queries.

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [December 18, 2019, 11:30am UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/3 "2019-12-18T11:30:12Z")

</div>

Hi @EliuFlorez.

Its very simple to your use case:-

`open kibana and try with below link and (sql query) it will auto convert into DSL/JSON FORMAT. `

[Translate SQL](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html)

Thanks  
HadoopHelp

---

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 18, 2019, 2:04pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/4 "2019-12-18T14:04:45Z")

</div>

Thanks.

---

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 18, 2019, 2:04pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/5 "2019-12-18T14:04:59Z")

</div>

Thenks

---

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 18, 2019, 8:06pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/6 "2019-12-18T20:06:11Z")

</div>

@matriv Hello, a question in the case of assignment of variables as in Mysql 'AS' can be implemented in ES?  
**Example:**

```auto
POST /_sql/translate
{
  "query": "SELECT id AS IID FROM gic_category WHERE IID != 1 ORDER BY IID DESC LIMIT 1"
}

```

---

<div class="post-metadata">

**Author:** ![rameshkr1994](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rameshkr1994/32/59029_2.png) [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)\
**Post date:** [December 19, 2019, 8:00am UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/7 "2019-12-19T08:00:29Z")

</div>

Hi @EliuFlorez .

I think no...

by using translate\_sql we can't convert IT.

may be some other guys have some idea about .

Thanks  
HadoopHelp

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [December 19, 2019, 11:10am UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/8 "2019-12-19T11:10:19Z")

</div>

With `_sql/translate` you won't see any difference, but if you execute your query with `/_sql` you will get back the alias `IID` you defined as column name.

---

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 19, 2019, 1:45pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/9 "2019-12-19T13:45:56Z")

</div>

Hi, I don't know why, but I don't assign the ES alias of SQL to ES.

**Example**

```auto
POST /_sql/translate
{
  "query": "SELECT id AS IID FROM gic_category WHERE IID != 1 ORDER BY IID DESC LIMIT 1"
}

```

**Response**

```auto
{
  "size" : 1,
  "query" : {
    "bool" : {
      "must_not" : [
        {
          "term" : {
            "id" : {
              "value" : 1,
              "boost" : 1.0
            }
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : {
    "includes" : [
      "id"
    ],
    "excludes" : []
  },
  "sort" : [
    {
      "id" : {
        "order" : "desc",
        "missing" : "_first",
        "unmapped_type" : "long"
      }
    }
  ]
}

```

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [December 19, 2019, 2:11pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/10 "2019-12-19T14:11:13Z")

</div>

As I've said with `/_sql/translate` you won't see any difference with/without the `IID` alias.  
You have to execute the query using `/_sql` without the `translate` and then you'll get a result where the column name is `IID` (the alias).

Check [available formats](https://www.elastic.co/guide/en/elasticsearch/reference/7.5/sql-rest-format.html) for the sql endpoint responses.

---

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 19, 2019, 2:27pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/11 "2019-12-19T14:27:08Z")

</div>

Okay. perfect. but my question is how can I tell ES in a JSON vs. ES query. that my field of name ' **id**' returns it to me as an alias ' **IID**'

**Example**

```auto
{
  "size" : 1,
  "query" : {
    "term" : {
      "id" : 1,
    }
  },
  "_source" : {
    "includes" : [
      **"id" AS "IDD",**
    ],
    "excludes" : []
  }
}

```

**Response**

```auto
{
  "took" : 6,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_index",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          **"IID": 2,**
          "date" : "2019-12-01T06:30:00Z"
        }
      },
      ....
    ]
  }
}

```

Something like that more or less I would like. Since by default the system has implementing MySQL then I want to implement a plugins which converts from SQL to ES in JSON and make the query directly to ES with the new structure in JSON to perform the query.

**☹**

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [December 19, 2019, 3:21pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/12 "2019-12-19T15:21:35Z")

</div>

There is NO way currently to alias a field at query time through the ES search API.  
You can find a couple of open issues in this area [here](https://github.com/elastic/elasticsearch/issues/49264) and [here](https://github.com/elastic/elasticsearch/issues/49028).

If your aliases are static you have the option to define [field aliases using the index mapping](https://www.elastic.co/guide/en/elasticsearch/reference/7.5/alias.html) and then you can use the [stored\_fields](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-store.html) to retrieve the fields you want by their alias.  
Please notice that using the field aliases won't allow you to retrieve the original field from `_source` by its aliased name.

---

<div class="post-metadata">

**Author:** ![EliuFlorez](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliuflorez/32/40884_2.png) [@EliuFlorez](https://discuss.elastic.co/u/EliuFlorez)\
**Post date:** [December 19, 2019, 3:28pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/13 "2019-12-19T15:28:59Z")

</div>

Thanks. 😃

---

<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 16, 2020, 3:30pm UTC](https://discuss.elastic.co/t/sql-structure-to-json-structure-the-elasticsearch/212037/14 "2020-01-16T15:30:51Z")

</div>

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