Add filter in query


(Sergey) #1

Hi!
I have my request:
{
"query": {
"range": {
"@timestamp": {
"gt": "now-8h"
}
}
},
"filter" :{
"term": {
"user-id": "661474"
}
},
"sort": [
{
"@timestamp": {
"order": "asc"
}
}
]
}
and I want to add filter for display only document with strings "log out" and "log in" in "message" field.
But i can't understand where i have to put this filter in my request.
Help please!


(Dan Tuffery) #2

Filters execute exact matches on fields, so if you don't have it already you will need an untokenized copy of the field in the index. You can do this by using the not_analyzed index attribute in your mapping.

Use a filtered query, this will execute the filter first (filters are cached) and only searches on the filtered documents which is more performant than a post filter. You also need the bool filter with must clauses in your query as you have more than one filter. As you want to filter on one field with two values the terms filter is the best fit for your use case:

{
    "query": {
        "filtered": {
            "query": {
                "range": {
                    "@timestamp": {
                        "gt": "now-8h"
                    }
                }
            },
            "filter": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "user-id": "661474"
                            }
                        },
                        {
                            "terms": {
                                "message": [
                                    "log in",
                                    "log out"
                                ]
                            }
                        }
                    ]
                }
            }
        }
    },
    "sort": [
        {
            "@timestamp": {
                "order": "asc"
            }
        }
    ]
}

(Sergey) #3

@dantuff
Thanks, if "log out" and "log in" is the part of message field (not whole string), how I can replace "term"?


(Dan Tuffery) #4

You could use a query filter (assuming that scoring is not important to you) and add multiple phrase match clauses to a bool query:

{
    "query": {
        "filtered": {
            "query": {
                "range": {
                    "@timestamp": {
                        "gt": "now-8h"
                    }
                }
            },
            "filter": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "user-id": "661474"
                            }
                        },
                        {
                            "query": {
                                "bool": {
                                    "should": [
                                        {
                                            "match_phrase": {
                                                "message": "log in"
                                            }
                                        },
                                        {
                                            "match_phrase": {
                                                "message": "log out"
                                            }
                                        }
                                    ]
                                }
                            }
                        }
                    ]
                }
            }
        }
    },
    "sort": [
        {
            "@timestamp": {
                "order": "asc"
            }
        }
    ]
}

(system) #5