Count aggregation on reverse nesting field for multiple values

Question

Our Elastic Mapping

 {"mappings": {
         "products" : {
          "properties":{
             "name " : {
               "type" : "keyword"
             },
             "resellers" : { 
                 "type" : "nested",
                 "properties" : {
                     "name" : { "type" : "text" },
                     "price" : { "type" : "double" }
                 }
             }
           }
         }
     }}

In this mapping each product stores the list of resellers which are selling it at specific price.

We have requirement where we want to get count of products sell by specific resellers at specific price, I am able to get it for single reseller by using reverse nested agg and cardinality agg using following query DSL . For ex:- Getting Total Product sell by Amazon at 2.

 {
      "query": {
        "bool": {
          "must": [
            {
              "match_all": {
                "boost": 1.0
              }
            }
          ]
        }
      },
      "aggs": {
        "patchFilter": {
          "nested": {
            "path": "resellers"
          },
          "aggs": {
            "nestedfilter": {
              "filter": {
               "bool": {
                 "must":[
                   {
                     "term" :{
                       "resellers.name.keyword": {
                      "value": "Amazon"
                    }
                     }
                   },{
                     "terms" :{
                       "resellers.price":[2]
                     }
                   }
                   ]
               }
              },
              "aggs": {
               "resellerprice": {
                        "reverse_nested" :{},
                     "aggs": {
                       "resellers_price":{
                               "cardinality" : {
                                    "field" : "name.keyword"
                                 }
                             }
                }
              }
            }
          }
        }
      }
    }
    }

I want to fetch it for multiple resellers(Amazon,Flipkart, Walmart) which are selling at 2 in single query. Can somebody help me out in doing that?

Answer 1

Mapping:

PUT productreseller
{
  "mappings": {
      "properties": {
        "name": {
          "type": "keyword"
        },
        "resellers": {
          "type": "nested",
          "properties": {
            "name": {
              "type": "text",
               "fields":{
                 "keyword":{
                   "type": "keyword"
                 }
               }
            },
            "price": {
              "type": "double"
            }
          }
        }
      }
    }
  }

Data:

 [
      {
        "_index" : "productreseller",
        "_type" : "_doc",
        "_id" : "JNbCLm0B00idyGV0Pn1Z",
        "_score" : 1.0,
        "_source" : {
          "name" : "P2",
          "resellers" : [
            {
              "name" : "amazon",
              "price" : 3
            },
            {
              "name" : "abc",
              "price" : 2
            }
          ]
        }
      },
      {
        "_index" : "productreseller",
        "_type" : "_doc",
        "_id" : "JdbCLm0B00idyGV0Wn0y",
        "_score" : 1.0,
        "_source" : {
          "name" : "P1",
          "resellers" : [
            {
              "name" : "amazon",
              "price" : 2
            },
            {
              "name" : "abc",
              "price" : 3
            }
          ]
        }
      },
      {
        "_index" : "productreseller",
        "_type" : "_doc",
        "_id" : "JtbPLm0B00idyGV0D32Y",
        "_score" : 1.0,
        "_source" : {
          "name" : "P4",
          "resellers" : [
            {
              "name" : "xyz",
              "price" : 2
            },
            {
              "name" : "abc",
              "price" : 3
            }
          ]
        }
      }
]

Query:

GET productreseller/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match_all": {
            "boost": 1
          }
        }
      ]
    }
  },
  "aggs": {
    "patchFilter": {
      "nested": {
        "path": "resellers"
      },
      "aggs": {
        "nestedfilter": {
          "filter": {
            "bool": {
              "must": [
                {
                  "terms": {
                    "resellers.price": [
                      2
                    ]
                  }
                }
              ]
            }
          },
          "aggs": {
            "NAME": {
              "terms": {
 --->terms aggregation to list resellers and reverse_nested as subaggregation
                "field": "resellers.name.keyword",
                "size": 10
              },
              "aggs": {
                "resellerprice": {
                  "reverse_nested": {},
                  "aggs": {
                    "resellers_price": {
                      "cardinality": {
                        "field": "name"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Result:

"aggregations" : {
    "patchFilter" : {
      "doc_count" : 8,
      "nestedfilter" : {
        "doc_count" : 3,
        "NAME" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : "abc",
              "doc_count" : 1,
              "resellerprice" : {
                "doc_count" : 1,
                "resellers_price" : {
                  "value" : 1
                }
              }
            },
            {
              "key" : "amazon",
              "doc_count" : 1,
              "resellerprice" : {
                "doc_count" : 1,
                "resellers_price" : {
                  "value" : 1
                }
              }
            },
            {
              "key" : "xyz",
              "doc_count" : 1,
              "resellerprice" : {
                "doc_count" : 1,
                "resellers_price" : {
                  "value" : 1
                }
              }
            }
          ]
        }
      }
    }
  }

If you want to display only certain resellers, you can add terms query in nested filter