Complex queries inside nested fields

I have some documents which contain a nested field planList which is an array of objects. It's length will always be 2 or 3. And the objects always have 3 keys - planName, planType, and planId.

I want to write a query which will return a list of all planNames, along with the most frequent plan that it was present with and it's frequency.

For example, consider the following 4 documents -

{planList: [{planName: a, planType: x, planId: 1},{planName: b, planType: x, planId: 2}]}
{planList: [{planName: a, planType: x, planId: 1},{planName: b, planType: x, planId: 2},{planName: c, planType: y, planId: 3}]}
{planList: [{planName: a, planType: x, planId: 1},{planName: c, planType: y, planId: 3}]}
{planList: [{planName: d, planType: y, planId: 4},{planName: c, planType: y, planId: 3}]}

The response for this data should be something like -
plan A was found with plan B and plan C 2 times (draw between plan B and C)
plan B was found with plan A 2 times
plan C was found with plan A 2 times
plan D was found with plan C one time

The query to get to these docs is very simple -

"query": {
    "bool": {
      "must": [
        {
          "match": {
            "event": "comparePlans"
          }
        }
      ]
    }
  }

Can someone please help me with the aggregations needed to get the insight I'm looking for?
Thanks.

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