SQL Structure to JSON Structure the ElasticSearch?

Elastic Stack Elasticsearch
1

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

2

I think it would be helpful to use the /_sql/translate endpoint and see how your SQL queries can be translated to ES search and aggregation queries.

1 Like
3

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

Thanks
HadoopHelp

1 Like
4

Thanks.

5

Thenks

6

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

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

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

8

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.

9

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

Example

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

Response

{
  "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"
      }
    }
  ]
}
10

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 for the sql endpoint responses.

11

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

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

Response

{
  "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.

:frowning:

12

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 and here.

If your aliases are static you have the option to define field aliases using the index mapping and then you can use the stored_fields 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.

13

Thanks. :smiley:

14

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