Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Elastic Search Sum aggregation with group by and where condition

I am newbie in ElasticSearch.

We are currently moving our code from relational DB to ElasticSearch. So we are converting our queries in ElasticSearch query format.

I am looking for ElasticSearch equivalent of below query -

SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL 
    AND ListPrice != 0.00 
    AND Name LIKE 'Mountain%'
GROUP BY Color

Can someone provide me the example of ElasticSearch query for above?

like image 301
Sameer Deshmukh Avatar asked May 26 '15 19:05

Sameer Deshmukh


People also ask

How do I use aggregation in Elasticsearch?

Terms Aggregation Once you select a field, it will generate buckets for each of the values and place all of the records separately. In our example, we have run the terms aggregation on the field “user” which holds the name of users. In return, we have buckets for each user, each with their document counts.

How Elasticsearch aggregation works internally?

Elasticsearch processes searches (queries) and aggregations in the same way: scatter, then gather. The first node to receive an incoming request will act as the coordinating node. The coordinating node's job is to parse the request, then route it as necessary (if necessary).

What is sub aggregation in Elasticsearch?

Sub-aggregations allow you to continuously refine and separate groups of criteria of interest, then apply metrics at various levels in the aggregation hierarchy to generate your report.

How do you do aggregation in Kibana?

Choose the type of visualization you want to create, then use the editor to configure the options. On the dashboard, click All types > Aggregation based. Select the visualization type you want to create. Select the data source you want to visualize.


1 Answers

You'd have a products index with a product type documents whose mapping could look like this based on your query above:

curl -XPUT localhost:9200/products -d '
{
  "mappings": {
    "product": {
      "properties": {
        "Color": {
          "type": "string"
        },
        "Name": {
          "type": "string"
        },
        "ListPrice": {
          "type": "double"
        },
        "StandardCost": {
          "type": "double"
        }
      }
    }
  }
}'

Then the ES query equivalent to the SQL one you gave above would look like this:

{
  "query": {
    "filtered": {
      "query": {
        "query_string": {
          "default_field": "Name",
          "query": "Mountain*"
        }
      },
      "filter": {
        "bool": {
          "must_not": [
            {
              "missing": {
                "field": "Color"
              }
            },
            {
              "term": {
                "ListPrice": 0
              }
            }
          ]
        }
      }
    }
  },
  "aggs": {
    "by_color": {
      "terms": {
        "field": "Color"
      },
      "aggs": {
        "total_price": {
          "sum": {
            "field": "ListPrice"
          }
        },
        "total_cost": {
          "sum": {
            "field": "StandardCost"
          }
        }
      }
    }
  }
}
like image 162
Val Avatar answered Oct 21 '22 23:10

Val