Counting documents that match both buckets at same time

I have this example list of documents:

curl -XPOST 'localhost:9200/test_data/test_row/_bulk?pretty' -H 'Content-Type: application/json' -d'
{ "index": {}}
{ "value": 1, "user_id": 1, "profiler_id": 1}
{ "index": {}}
{ "value": 3, "user_id": 1, "profiler_id": 1}
{ "index": {}}
{ "value": 3, "user_id": 1, "profiler_id": 2}
{ "index": {}}
{ "value": 1, "user_id": 2, "profiler_id": 1}
{ "index": {}}
{ "value": 1, "user_id": 2, "profiler_id": 2}
{ "index": {}}
{ "value": 1, "user_id": 3, "profiler_id": 1}
{ "index": {}}
{ "value": 2, "user_id": 3, "profiler_id": 2}
{ "index": {}}
{ "value": 3, "user_id": 3, "profiler_id": 2}
{ "index": {}}
{ "value": 2, "user_id": 4, "profiler_id": 1}
{ "index": {}}
{ "value": 1, "user_id": 4, "profiler_id": 2}
{ "index": {}}
{ "value": 3, "user_id": 4, "profiler_id": 2}
'

I want to count all users that answered profiler_id=1 and value=1 AND profiler_id=2 and value 1 or 2. This means that only users 2 and 3 match the criteria for both.

I know how to make a bucket aggregation on terms and count matching documents per profiler_id but is there a way in Elasticsearch to count users matching BOTH criteria at once?

{
  "query": {
    "bool": {
      "must": [],
      "must_not": [],
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "profiler_id": 1
                }
              },
              {
                "term": {
                  "value": 1
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "profiler_id": 2
                }
              },
              {
                "terms": {
                  "value": [
                    1,
                    2
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "size": 0,
  "aggs": {
    "genres": {
      "terms": {
        "field": "profiler_id"
      },
      "aggs": {
        "type_count": {
          "cardinality": {
            "field": "user_id"
          }
        }
      }
    }
  }
}

Would give me the counts separately per profiler_id which is not what I want. I'm not sure how to count users that have documents intersecting in both buckets.

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