CODEWITHSUNDEEP
elasticsearch
santoshM
santoshM

Reputation: 267

Groupby functionality on multiple fields in elastic search

I have a requirement where I need to groupby status_value as per regions and regions as per given date. For the same I have written a query and it is not exactly working in the ES. It would be a great help, if someone look into this and provide me with the solution.

Note: I would like to get the result for the last day (i.e. previous day).

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "term": {
          "UsagePoint_Asset_lifecycle_installationDate": "2014-07-13T16:55:00.0-07:00"
        }
      }
    }
  },
    "aggs" : {
        "product" : {
            "terms" : {
                "field" : "UsagePoint_ServiceLocation_region"
            },
            "aggs" : {
                "material" : {
                    "terms" : {
                        "field" : "UsagePoint_status_value"
                    }
                }
            }
        }       
    }
}

my sql query may be like below:

select count(status_value)
from products 
where date = "yesterday"
group by region , date

Please check below query is working, but I would like to get the values for a specific day or dates.

{
    "agg1": {
        "terms": {
           "field":"UsagePoint_Asset_lifecycle_installationDate"
        },
        "aggs" : {
        "product" : {
            "terms" : {
                "field" : "UsagePoint_ServiceLocation_region"
            },
            "aggs" : {
                "material" : {
                    "terms" : {
                        "field" : "UsagePoint_status_value"
                    }
                }
            }
        }       
    }
    }
}

Upvotes: 0

Views: 78

Answers (1)

Vineeth Mohan
Vineeth Mohan

Reputation: 19253

If you need the group by info for yesterday , the following is a solution. For any other custom dates , change the value in gte ( Greater than or equals to ) and lt ( Less than )

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
      "filter": {
        "range": {
          "UsagePoint_Asset_lifecycle_installationDate": {
            "gte": "now-1d",
            "lt": "now"
          }
        }
      }
    }
  },
  "aggs": {
    "product": {
      "terms": {
        "field": "UsagePoint_ServiceLocation_region"
      },
      "aggs": {
        "material": {
          "terms": {
            "field": "UsagePoint_status_value"
          }
        }
      }
    }
  }
}

Upvotes: 1

Related Questions