Filtering out results

[Justin_Cross]

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.

[Justin_Cross]

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

[Ivan (Ivan Brusic)]

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
[2]
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
[4]
https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-collapse.html

Cheers,

Ivan

[Mark_Harwood]

You can bring related events together using an entity centric indexing approach
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.