SQL Field aliases in ES?

Elastic Stack Elasticsearch
1

Hi guys, I am writing to make this query on how to set the aliases in the ES queries. since I want to implement the conversion from SQL to ES through a PHP plugin in development. For the application to be implemented, it has many aliases established in its SQL and JOIN queries. For the JOIN we will denormalize the structure in the actions of INSERT and UPDATE by establishing the document with all its related information. But now the problems present are the aliases that have thousands of SQL queries. Any idea how to set such aliases in ES.

Example:

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

On the methods of time what would be your conversion into ES?

(UNIX_TIMESTAMP(DATE_ADD(`date`, INTERVAL `period` DAY)) > UNIX_TIMESTAMP(NOW())

How can I set the range against an existing fields in the same document?

GET my_index/_search
{
    "query": {
        "range" : {
            "date" : {
                "gte" : "now-[period]d/d",
                "lte" :  "now/d"
            }
        }
    }
}
2

I am not sure what you mean by the problems present are the aliases that have thousands of SQL queries. Elasticsearch SQL knows how to handle aliases, but maybe I don't understand your concern here.

UNIX_TIMESTAMP doesn't have a direct correspondent in Elasticsearch SQL, but because dates are kept as millis since epoch in ES by default, you can do something like CAST((date + INTERVAL 5 DAY) AS LONG) / 1000 > CAST(NOW() AS LONG) / 1000.

Regarding ranges, I would appreciate it if you can provide a SQL example with ranges that either don't work or you are concerned about...

3

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