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?
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.
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).
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.
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.
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"
}
}
}
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With