# How to Execute Query to fetch Intersection of Two DataSets

**URL:** <https://discuss.elastic.co/t/how-to-execute-query-to-fetch-intersection-of-two-datasets/37308>\
**Category:** Elasticsearch\
**Created:** [December 16, 2015, 6:42am UTC](https://discuss.elastic.co/t/how-to-execute-query-to-fetch-intersection-of-two-datasets/37308 "2015-12-16T06:42:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kpcool](https://avatars.discourse-cdn.com/v4/letter/k/90db22/32.png) [@kpcool](https://discuss.elastic.co/u/kpcool)\
**Post date:** [December 16, 2015, 6:42am UTC](https://discuss.elastic.co/t/how-to-execute-query-to-fetch-intersection-of-two-datasets/37308/1 "2015-12-16T06:42:45Z")

</div>

I have an index 'analytics', which contains a list of events ( for eg: CRUD) that occured over a period of time. I am looking to find a set of records that were added and deleted by primary key.

document structure:

> id, key, event, timestamp

where key is primary key of record, event is 'create', 'read', 'delete', 'update'.

I want to find the list of primary keys that were both 'created' and 'deleted'. Basically an intersection of two sets ('created') and ('deleted') over the primary key.

I can't seem to get ahead with this.

---

<div class="post-metadata">

**Author:** ![shaunak](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shaunak/32/6643_2.png) [@shaunak](https://discuss.elastic.co/u/shaunak)\
**Post date:** [December 16, 2015, 1:20pm UTC](https://discuss.elastic.co/t/how-to-execute-query-to-fetch-intersection-of-two-datasets/37308/2 "2015-12-16T13:20:59Z")

</div>

You could try a query with aggregations like this:

```
{
  "size": 0, 
  "query": {
    "bool": {
      "filter": {
        "terms": {
          "event": [
            "create",
            "delete"
          ]
        }
      }
    }
  },
  "aggs": {
    "same_key": {
      "terms": {
        "field": "key",
        "min_doc_count": 2, 
        "size": 100
      }
    }
  }
} 

```

This query first filters documents that are only `create` or `delete` events. Then it aggregates these documents by `key`. You want only those keys that have both these events, hence the `min_doc_count` value is 2.

You may want to tweak the `size` in the terms aggregation (set to 100 above) per your needs.

BTW, the above syntax works for Elasticsearch 2.x. For older versions of Elasticsearch, you will need to use the `filtered` query instead of the `bool` query but everything else will remain the same.

---

<div class="post-metadata">

**Author:** ![Raj2](https://avatars.discourse-cdn.com/v4/letter/r/74df32/32.png) [@Raj2](https://discuss.elastic.co/u/Raj2)\
**Post date:** [April 17, 2017, 7:15am UTC](https://discuss.elastic.co/t/how-to-execute-query-to-fetch-intersection-of-two-datasets/37308/3 "2017-04-17T07:15:13Z")

</div>

Generally I use |A intersect B| = |A| + |B| - | A U B|, but you have to be careful if you are doing cardinality aggregations, as you can get negative values.

---

<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:** [July 5, 2017, 10:01pm UTC](https://discuss.elastic.co/t/how-to-execute-query-to-fetch-intersection-of-two-datasets/37308/4 "2017-07-05T22:01:10Z")

</div>