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

Did you try using the Kibana Query Language (KQL). Use the following ACCOUNT:'Monitoring' and APPLICATION_NAME:'webapp' in the search that might sort. — Sarath Chandra, Mar 28 '22 at 21:35

Amit · Answer 1 · 2022-03-25T08:50:10.783

If I understand it correctly, you want to see all the fields in your index named XXX, after applying the filter values monitoring and webApp.

You don't have to do anything as Elasticsearch by default, returns the _source (which contains all the values ie fields in your document JSON).

Example:

Index sample docs

{
    "name": "foo",
    "title": "bar",
    "account" : "monitoring",
    "app" : "webapp"
}
{
    "name": "abc",
    "title": "def",
    "account" : "observe",
    "app" : "webapp"
}

And search query according to your requirement(some filters).

{
    "query": {
        "bool": {
            "filter": [
                {
                    "term": {
                        "account": "monitoring"
                    }
                },
                {
                    "term": {
                        "app": "webapp"
                    }
                }
            ]
        }
    }
}

And search result

"hits": [
            {
                "_index": "71614025",
                "_id": "1",
                "_score": 0.0,
                "_source": {
                    "name": "foo",
                    "title": "bar",
                    "account": "monitoring",
                    "app": "webapp"
                }
            }
        ]

Notice all the indexed fields in your search result, under the hits section in the search response.

No :) unfortunately, you didn't get the question, I don't want to select the whole record, I only want one field to be extracted from the record that this filter matches it. the result should be a list of values of the filename field. — Shima Mahmoud, Mar 25 '22 at 22:29

score 0 · Accepted Answer · answered Apr 01 '22 at 14:13

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"
                    }
                  }
                }
              ]
            }
          }
        
        }

select a single field with applying filters in elasticsearch

2 Answers2