CODEWITHSUNDEEP
sqlelasticsearch
user3921220
user3921220

Reputation: 21

Elastic search query filter

I am new in elastic search.

How I can convert the following SQL statement into elastic search query?

select sum(totaldevicecount),datasource from
(select distinct oskey,custkey,productkey,
timekey,totaldevicecount,datasource from es_reporting_data_new) 
group by datasource;

Thanks

Upvotes: 2

Views: 891

Answers (4)

Ajay Kewat
Ajay Kewat

Reputation: 1

Logstash input support JDBC inputs. One way to execute the queries and load the resultset is supported by ELK. You can directly use the queries or preferred views for larger queries. Your output will be directly ingested into ELK. https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html

The other way, if you want to query the indexes, you can writes queries directly in Dev tools like below:

POST _xpack/sql?format=txt
{
  "query":"DESCRIBE \"indice-name-*\""
}

POST _xpack/sql
{
  "query":"Select data1, data2 from \"indice-name-*\" where data1 = 'ABC' and ID = '11223333'"
}

Upvotes: 0

Asad
Asad

Reputation: 41

After simplifying your query to below

select sum(totaldevicecount),datasource from es_reporting_data_new group by datasource;

ES query would be 

{
    "aggs": {
            "data_source": {
                "terms": {
                    "field": "datasource"
                },
                "aggs": {
                    "total_device_count": {
                        "sum": {
                            "field": "totaldevicecount"
                        }
                    }
                }
            }
        }
    }

For more details see also Elastic Search Sum aggregation with group by and where condition.

Upvotes: 2

Nitin
Nitin

Reputation: 19

As answer is already provided. I'll add extra bit. I would highly recommend reading elastic documenatation assuming you need to convert many queries like this. As elastic query parameter is different from sql. So in depth understanding of elastic query param will help you convert SQL query if possible.

Upvotes: 0

yalkris
yalkris

Reputation: 2715

terms query for distinct, filters for groupBy and aggregations for nested selects and sum if I remember correctly.

Upvotes: 0

Related Questions