# Filtering out results

**URL:** <https://discuss.elastic.co/t/filtering-out-results/101222>\
**Category:** Elasticsearch\
**Created:** [September 20, 2017, 6:00pm UTC](https://discuss.elastic.co/t/filtering-out-results/101222 "2017-09-20T18:00:40Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Justin\_Cross](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_cross/32/104500_2.png) [@Justin\_Cross](https://discuss.elastic.co/u/Justin_Cross)\
**Post date:** [September 20, 2017, 6:00pm UTC](https://discuss.elastic.co/t/filtering-out-results/101222/1 "2017-09-20T18:00:41Z")

</div>

assuming documents like

id,event\_id,event\_name

1,1,EVENT\_A  
1,2,EVENT\_B  
1,3,EVENT\_C  
2,4,EVENT\_A  
2,5,EVENT\_B  
1,6,EVENT\_D  
3,7,EVENT\_A

how can i get all the results where id has EVENT\_B but not EVENT\_C

For example, in the above results I would want back is:  
2,5,EVENT\_B

because id 2 doesn't have EVENT\_C

I don't know how to do this in elasticsearch. Please help.

---

<div class="post-metadata">

**Author:** ![Justin\_Cross](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/justin_cross/32/104500_2.png) [@Justin\_Cross](https://discuss.elastic.co/u/Justin_Cross)\
**Post date:** [September 21, 2017, 11:20am UTC](https://discuss.elastic.co/t/filtering-out-results/101222/2 "2017-09-21T11:20:52Z")

</div>

How can I make ElasticSearch only return hits containing my aggregation filter. Basically I only want to see hits/results that contain the event\_names that show up in my aggregations. Please help.

Here is my query for now

```
{
"from": 0,
"size": 1,
"sort": [{
    "events_rcrd_crtd_ts": {
        "order": "desc"
    }
}, {
    "event_id": {
        "order": "desc"
    }
}],
"aggs": {
    "id.raw": {
        "terms": {
            "field": "id.raw",
            "size": 0
        },
        "aggs": {
            "id_bucket_filter": {
                "bucket_selector": {
                    "buckets_path": {
                        "count": "_count"
                    },
                    "script": {
                        "inline": "count < 2"
                    }
                }
            }
        }
    }
}
}
```

---

<div class="post-metadata">

**Author:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\
**Post date:** [September 21, 2017, 3:14pm UTC](https://discuss.elastic.co/t/filtering-out-results/101222/3 "2017-09-21T15:14:43Z")

</div>

To answer your first question, it is not possible to have queries that  
cross reference other documents. In this case, it would help to denormalize  
your data, and to have your event objects as nested objects [1].

{  
"id" : 1,  
"events" : [  
{  
"id" : 1,  
"event\_name" : "A"  
},  
{  
"id" : 2,  
"event\_name" : "B"  
}  
]  
}

With this structure, you can now use a nested query [2].

To answer your second question, you can use the top hits aggregation [3].  
Also look into field collapsing [4].

[1]  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html)  
[2]  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-nested-query.html)  
[3]  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html)  
[4]  
[https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-collapse.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-collapse.html)

Cheers,

Ivan

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [September 21, 2017, 3:56pm UTC](https://discuss.elastic.co/t/filtering-out-results/101222/4 "2017-09-21T15:56:02Z")

</div>

You can bring related events together using an[entity centric indexing approach](https://www.youtube.com/watch?v=yBf7oeJKH2Y)  
Nested docs are not necessary as long as your queries don't test \>1 property in each event object (your example was only testing a single property "event\_name"). The event names could live in a simple string array.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 19, 2017, 4:06pm UTC](https://discuss.elastic.co/t/filtering-out-results/101222/5 "2017-10-19T16:06:54Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.