Group multiple document that shares same id in a field using ElasticSearch

Question

We are having a set of data, as below in ElasticSearch. Its list of products indexed from an ecommerce backend.

“hts” : [
{
   "_index": "test",
    "_type": "commerce_products_index",
    "_id": "466174",
    "_score": 1,
"_source": {
      "id": 261776,
      "changed": "1516367458",
      "commerce_price:amount": "2700",
      "field_product_node:nid": [
        "66741"
      ],
      "field_uom_type": "g",
      "field_weight": "337",
      "product_id": "261776",
      "title": "Brown Lobia",
    }
  },
{
   "_index": "test",
    "_type": "commerce_products_index",
    "_id": "466175",
    "_score": 1,
   "_source": {
      "id": 261781,
      "changed": "1526448108",
      "commerce_price:amount": "5900",
      "field_product_node:nid": [
        "66741"
      ],
      "field_uom_type": "g",
      "field_weight": "339",
      "product_id": "261781",
      "title": "Brown Lobia",
    }
 },
 {
   "_index": "test",
    "_type": "commerce_products_index",
    "_id": "466176",
    "_score": 1,
   "_source": {
      "id": 466176,
      "changed": "1515568794",
      "commerce_price:amount": "5400",
      "commerce_store": "651",
      "field_product_node:nid": [
        "84651"
      ],
      "field_uom_type": "g",
      "field_weight": "337",
      "product_id": "466176",
      "title": "Maggi Rich Tomato Ketchup",
    }
  }
]

As you can see, first 2 documents has the field_product_node:nid same. (i.e 66741). This are two different sizes(variations) of the same product.

In search we want to show these same product as one. For it, we need to the result with the field field_product_node:nid, which will be unique for each products that are same. For example, White Rice 1 Kg & White Rice 500g will have same value in field_product_node:nid. So when searched both the product details should be grouped under one nid.

Currently, we are getting different documents per product. However, we want to get both products as a single document.

We tried following queries:

GET /commerce_products_index/_search
{
  "size": 20, 
  "query" : {
    "bool": {
      "must": [
        { "match": { "commerce_store": "651"}}
      ]
     }
   },
  "aggs": {
    "group_by_node": {
      "terms": {
        "field": "field_product_node:nid"
      }
    }
  }
}

GET /commerce_products_index/_search
 {
   "aggregations": {
     "grp_report": {
       "terms": {
         "field": "field_product_node:nid"
       },
      "aggregations": {
        "nested_node": {
          "nested": {
            "path": "node"
          },
        "aggregations": {
          "filters_customer": {
            "filters": {
              "filters": [
               {
                  "match": {
                    "node.commerce_store": "651"
                  }
                }
              ]
            }
          }
        }
      }
    }
  }
 },
  "query" : {
     "bool": {
       "must": [
         { "match": { "commerce_store": "651"}}
       ]
     }
   },
  "from": 0,
  "size": 100
}

We are not able to figure out the right method. If this isn't possible we have to go ahead and redevelop the indexing part and try index multiple products with same nid into a single document. It will be a considerable rewrite.

Answer 1

I have tried following query. It worked for our issue.

GET /commerce_products_index/_search
{       
   "size": 20,        
   "aggs": {
     "by_node": {
       "terms": {
         "field": "field_product_node:nid",
         "size": 11,
         "order": {
          "max_score": "desc"
         }
       },
       "aggs": {
         "by_top_hit": {
         "top_hits": {
         "size": 15
        }
      },
      "max_score": {
        "max": {
          "field": "field_product_node:nid",
          "script": "_score"
         }
       }
      }
    }
  }
 }

It may help someone who is facing same issue.