CODEWITHSUNDEEP
elasticsearch
hisna
hisna

Reputation: 31

ElasticSearch Query to find intersection of two queries

Records exist in this format: {user_id, state}.

I need to write an elasticsearch query to find all user_id's that have both states present in the records list.

For example, if sample records stored are:

{1,a}
{1,b}
{2,a}
{2,b}
{1,a}
{3,b}
{3,b}

The output from running the query for this example would be 

{"1", "2"}

I've tried this so far:

{
  "size": 0, 
  "query": {
    "bool": {
      "filter": {
        "terms": {
          "state": [
            "a",
            "b"
          ]
        }
      }
    }
  },
  "aggs": {
    "user_id_intersection": {
      "terms": {
        "field": "user_id",
        "min_doc_count": 2, 
        "size": 100
      }
    }
  }
}

but this will return 

{"1", "2", "3"}

Upvotes: 3

Views: 5762

Answers (1)

Ryadh Khsib
Ryadh Khsib

Reputation: 94

Assuming you know the cardinality of the states set, here 2, you can use the Bucket Selector Aggregation

GET test/_search
{
  "size": 0,
  "aggs": {
    "user_ids": {
      "terms": {
        "field": "user_id"
      },
      "aggs": {
        "states_card": {
          "cardinality": {
            "field": "state"
          }
        },
        "state_filter": {
          "bucket_selector": {
            "buckets_path": {
              "states_card": "states_card"
            },
            "script": "params.states_card == 2"
          }
        }
      }
    }
  }
}

Upvotes: 1

Related Questions