Elasticsearch / Opensearch - intersection of common values in arrays

Question

given

a = [1,2,3,4]

and

b = [1,3]
c = [5,6]
d = [1,6]

how can i find the exact number of common values of a with b,c,d in elasticsearch?

I would expect:

b -> 2
c -> 0
d -> 1

Answer 1

Let consider below is your elasticsearch document,

{
  "b": [1,3],
  "c": [5,6],
  "d": [1,6]
}

Your Elasticsearch Query will looks like below:

Here, You need to use first terms aggregation and above that you need to apply sum_bucket aggregation.

{
  "size": 0,
  "aggs": {
    "b_count": {
      "terms": {
        "field": "b",
        "size": 10,
        "include": [1,2,3,4]
      }
    },
    "c_count": {
      "terms": {
        "field": "c",
        "size": 10,
        "include": [1,2,3,4]
      }
    },
    "d_count": {
      "terms": {
        "field": "d",
        "size": 10,
        "include": [1,2,3,4]
      }
    },
    "b_sum": {
      "sum_bucket": {
        "buckets_path": "b_count>_count"
      }
    },
    "c_sum": {
      "sum_bucket": {
        "buckets_path": "c_count>_count"
      }
    },
    "d_sum": {
      "sum_bucket": {
        "buckets_path": "d_count>_count"
      }
    }
  }
}

Sample Response:

You can use value of b_sum, c_sum and d_sum from below response.

"aggregations" : {
    "b_count" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 1,
          "doc_count" : 1
        },
        {
          "key" : 3,
          "doc_count" : 1
        }
      ]
    },
    "d_count" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 1,
          "doc_count" : 1
        }
      ]
    },
    "c_count" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [ ]
    },
    "b_sum" : {
      "value" : 2.0
    },
    "c_sum" : {
      "value" : 0.0
    },
    "d_sum" : {
      "value" : 1.0
    }
  }