select a single field with applying filters in elasticsearch

Question

I would like to select all the filename field values by ACCOUNT and APPLICATION_NAME Assuming as in SQL I need to do this :

select filename.keyword from XXX where ACCOUNT='monitoring' and APPLICATION_NAME='webapp'

this is a screenshot of a log entry sample in the kibana interface

Answer 1

selecting the unique values of a specific field is exactly like running an aggregation query at one of the SQL databases for example

• selecting by field.keyword is something like passing an enum value that should exactly match one of the existing values against this field.

• setting size to 0 will retrieve the aggregation result only without associating with it the list of sources.

• in an agg query as I said above it is selecting one of the aggregation functions against some fields that could be one or more

• incase they are multiple this should become a composite aggregation.

• Composite aggregartion require specifing composite.sources in the query request body.

this query worked for me in case I wanted to selelct filename and POD_ID uniques pairs.

{
          "size": "0",
          "aggs": {
            "custom_agg_name_whatever_you_want": {
              "composite": {
                "sources": [
                  {
                    "FILENAME": {
                      "terms": {
                        "field": "filename.keyword"
                      }
                    }
                  },
                  {
                    "POD_ID":{
                      "terms": {
                        "field": "POD_ID.keyword"
                      }
                    }
                  }
                ]
              }
            }
          },
          
          "query": {
            "bool": {
              "filter": [
                {
                  "bool": {
                    "filter": [
                      {
                        "bool": {
                          "should": [
                            {
                              "match_phrase": {
                                "ACCOUNT.keyword": "searchValue"
                              }
                            }
                          ],
                          "minimum_should_match": 1
                        }
                      },
                      {
                        "bool": {
                          "should": [
                            {
                              "match_phrase": {
                                "APPLICATION_NAME.keyword": "searchValue"
                              }
                            }
                          ],
                          "minimum_should_match": 1
                        }
                      }
                    ]
                  }
                },
                {
                  "range": {
                    "@timestamp": {
                      "format": "strict_date_optional_time",
                      "gte": "2022-03-21T09:09:09.277Z",
                      "lte": "2022-03-25T09:09:09.277Z"
                    }
                  }
                }
              ]
            }
          }
        
        }