'convert sql to dsl elasticsearch query

i want to convert this sql query to elastic DSL query language 

SELECT t.pk_c_c_s,
       t.fk_c_c_id,
       t.s_b_a,
       t.datetime,
       SUBSTR(t.datetime, 0, 7) m,
       (
         SELECT SUM(i.s_b_a) sarpu
         FROM TBL_C_C_S i
         WHERE substr(i.datetime, 0, 7) = substr(t.datetime, 0, 7)
           AND i.datetime <= t.datetime
           AND i.fk_c_c_id = t.fk_c_c_id
         GROUP BY SUBSTR(i.datetime, 0, 7)
        ) s
FROM TBL_C_C_S t

how can i convert this sql query to elasticsearch

this is my way in elasticsearch

POST /c_c_s_index_test/_search
{ "size":0,
  "aggs": {
    "customer": {
      "terms": {
        "field": "fk_c_c_id",
        "size": 5
      },
      "aggs": {
        "sumscore": {
          "sum": {
            "field": "s_b_a"
          }
        },
        "month": {
          "date_histogram": {
            "field": "datetime",
            "interval": "1M",
            "min_doc_count": 1
          },
          "aggs": {
            "customer": {
              "sum": {
                "field": "s_b_a"
              }
            }
          }
        }
      }
    }    ,
        "stats_monthly_sales": {
            "extended_stats_bucket": {
                "buckets_path": "customer>sumscore" 
            }
        }
  }

but this just return sum of month and i.datetime<=t.datetime does not exists in this

sqlaggregation


Solution 1:[1]

What you probably need is cumulative sum aggregation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-cumulative-sum-aggregation.html

So your query should look as follows:

{
  "size": 0,
  "aggs": {
    "customer": {
      "terms": {
        "field": "fk_c_c_id",
        "size": 5
      },
      "aggs": {
        "sales_per_month": {
          "date_histogram": {
            "field": "datetime",
            "interval": "month"
          },
          "aggs": {
            "sales": {
              "sum": {
                "field": "s_b_a"
              }
            },
            "cumulative_sales": {
              "cumulative_sum": {
                "buckets_path": "sales"
              }
            }
          }
        }
      }
    }
  }
}

Solution 2:[2]

Using ES 7, you can translate your query to dsl using the _xpack/format as follows:

curl -H "Content-Type:application/json" -XPOST 127.0.0.1:9200/_xpack/sql/translate?pretty -d '{"query" : "SELECT t.pk_c_c_s,
       t.fk_c_c_id,
       t.s_b_a,
       t.datetime,
       SUBSTR(t.datetime, 0, 7) m,
       (
         SELECT SUM(i.s_b_a) sarpu
         FROM TBL_C_C_S i
         WHERE substr(i.datetime, 0, 7) = substr(t.datetime, 0, 7)
           AND i.datetime <= t.datetime
           AND i.fk_c_c_id = t.fk_c_c_id
         GROUP BY SUBSTR(i.datetime, 0, 7)
        ) s
FROM TBL_C_C_S t"}'

Solution 3:[3]

I think the ones coming to this discussion are looking for this solution. To anyone that wants to convert his SQL query to DSL.

There is an API to translate your SQL query provided by elasticsearch

You simply send a post request for example :

POST /_sql/translate
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 10
}

This will translate it into native Elasticsearch queries. Like this:

 {
  "size": 10,
  "_source": false,
  "fields": [
    {
      "field": "author"
    },
    {
      "field": "name"
    },
    {
      "field": "page_count"
    },
    {
      "field": "release_date",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "page_count": {
        "order": "desc",
        "missing": "_first",
        "unmapped_type": "short"
      }
    }
  ]
}

You can check out the documentation Here.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Evaldas Buinauskas
Solution 2 KayV
Solution 3 seghair tarek

Related Questions