Developer Developer - 3 months ago 13
SQL Question

SQL Where clause equivalent for Elastic Search

I am trying to create a aggregate results in elastic search but filter option is not working for me.

I can aggregate data without filter e.g.

select name , material ,sum(price)
from products group by name , material

curl -XGET 'http://localhost:9200/products/_search?pretty=true' -d'
{
"aggs" : {
"product" : {
"terms" : {
"field" : "name"
},
"aggs" : {
"material" : {
"terms" : {
"field" : "material"
},
"aggs" : {
"sum_price" : {
"sum" : {
"field" : "price"
}
}
}
}
}
}
},
"size" : 0
}'


but I am facing problems to write equivalent DSL query of :

select name , material ,sum(price)
from products
where material = "wood"
group by name , material

Answer

Should be something like this:

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "term": {
          "material": "wood"
        }
      }
    }
  },
    "aggs" : {
        "product" : {
            "terms" : {
                "field" : "name"
            },
            "aggs" : {
                "material" : {
                    "terms" : {
                        "field" : "material"
                    },
                    "aggs" : {
                        "sum_price" : {
                            "sum" : {
                                "field" : "price"
                            }
                        }
                    }
                }
            }
        }
    },
    "size" : 0
}

Use a filter if you know the exact value and do not need a match, else use a match query instead of the filtered query.

Comments