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?

Is there only one document for each unique product?

Nishant
– Nishant

2019-09-14 03:51:13 +00:00
Commented Sep 14, 2019 at 3:51 — Nishant
– Nishant, Commented Sep 14, 2019 at 3:51

jaspreet chahal · Accepted Answer · 2019-09-14 08:16:25Z

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

Collectives™ on Stack Overflow

Count aggregation on reverse nesting field for multiple values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related