Is it possible to make a template that aggregates by one field and sorts by another?

Elastic Stack Kibana
1

Hi,

We have a elasticsearch index with a lot of data in it. For this question, I am interested in two fields primarily: brand.brands_pk and brand.name_loc.content_default. I want to create a template to return a unique list of integer brand.brand_pks, and sort it by the text field brand.name_loc.content_default. There is a search text term, which brand.name_loc.content_default should be searchable on. Is this possible? So far, I have this queries, but it is giving me the following error message.

POST _scripts/BRANDS_FILTER
{
"script": {
"lang": "mustache",
"source": "{\"query\":{\"bool\":{\"must\": [{\"match\":{\"region_code.keyword\":\"{{regionCode}}\"}},{\"match\":{\"catalog_type.keyword\":\"{{catalogType}}\"}},{\"multi_match\":{\"query\":\"{{keyword}}\",\"fuzziness\":\"AUTO:3,8\",\"prefix_length\":2,\"type\":\"best_fields\",\"operator\":\"and\",\"fields\": [\"brand.name_loc.content_default^3.1\"]}}]}},\"aggs\": {\"unique_brand_ids\": {\"terms\": {\"field\": \"brand.brand_pk\",\"size\": {{size}},\"order\": {\"brand_name_sort\": \"asc\"}}},\"brand_name_sort\": {\"terms\": {\"field\": \"brand.name_loc.content_default.keyword\",\"order\": {\"_key\": \"asc\"}}}},\"from\": {{from}},\"size\": 0}",
"_source": ["brand.brand_pk", "brand.name_loc.content_default.keyword"],
"size": 1
}
}

GET axp_marketplace_search_catalog_1_1767637823_unique/_search/template
{
"id": "BRANDS_FILTER",
"params": {
"keyword": "the",
"regionCode": "US",
"catalogType": "CATALOG",
"fuzziness": true,
"from": 0,
"size": 100,
"sort": "\"brand.name_loc.content_default.keyword\""
}
}

Error message is as follows:
"Invalid aggregation order path [brand_name_sort]. The provided aggregation [brand_name_sort] either does not exist, or is a pipeline aggregation and cannot be used to sort the buckets."

Any help is appreciated.

2

Hello @Phil_McLachlan

Please share one sample document and the index mapping for the relevant fields (brand.brand_pk and brand.name_loc.content_default) to review this issue.

Thanks!!

3

Here is a document example:
"_source": {
"navigation_category": {
"name_loc": {
"fi-FI": "Keittiö ja ruokailu",
"nb-NO": "Kjøkken og spisestue",
"en-US": "Kitchen & Dining",
"content_default": "Kitchen & Dining",
"ms-MY": "Dapur & Ruang Makan",
"zh-CN": "厨房和餐厅",
"nl-NL": "Keuken en eetgerei",
"en-AU": "Kitchen and dining",
"he-IL": "מטבח ואוכל",
"cs-CZ": "Kuchyň a stolování",
"ja-JP": "キッチン、ダイニング",
"de-DE": "Küche und Esszimmer",
"hu-HU": "Konyha és étkező",
"zh-TW": "廚具和餐具",
"ko-KR": "주방 및 다이닝",
"pt-BR": "Mesa",
"es-ES": "Cocina y comedor",
"es-MX": "Cocina y comedor",
"fr-CA": "Cuisine et salle à manger",
"it-IT": "Cucina e sala da pranzo",
"pl-PL": "Kuchnia i jadalnia",
"ru-RU": "Кухня и столовая",
"pt-PT": "Cozinha e mesa",
"ro-RO": "Bucătărie și cameră de zi",
"sv-SE": "Kök och matsal",
"id-ID": "Perlengkapan Dapur & Makan",
"da-DK": "Køkken og spisning",
"tr-TR": "Mutfak",
"fr-FR": "Cuisine et salle à manger",
"vi-VN": "Nhà bếp và bộ đồ ăn",
"en-GB": "Kitchen & dining",
"th-TH": "เครื่องครัวและอุปกรณ์รับประทานอาหาร"
},
"navigation_category_pk": 55,
"navigation_category_list": [
378,
393,
55
]
},
"images": {
"image": "",
"thumbnail": ""
},
"redemption_count": 55,
"catalog_category": {
"catalog_category_pk": 55,
"name_loc": {
"pt-PT": "Cozinha e sala de jantar",
"en-US": "Kitchen & Dining",
"content_default": "Kitchen & Dining",
"es-ES": "Cocina",
"fr-FR": "Cuisine et séjour",
"en-GB": "Kitchen & Dining"
}
},
"is_eligible_for_top_up": true,
"catalog_date_created": "2026-01-05T18:24:55Z",
"last_image_upload_date_utc": "2020-02-27T16:41:23Z",
"type": "NORMAL",
"tags": [
{
"name_loc": {
"en-US": "Dining",
"content_default": "Dining",
"en-GB": "Dining",
"es-MX": "Comida",
"fr-CA": "À manger"
},
"tag_pk": 45
},
{
"name_loc": {
"de-DE": "merchandise",
"en-US": "merchandise",
"content_default": "merchandise",
"zh-TW": "merchandise",
"ko-KR": "merchandise",
"pt-BR": "merchandise",
"zh-CN": "merchandise",
"es-MX": "merchandise",
"fr-CA": "merchandise",
"it-IT": "merchandise",
"ru-RU": "merchandise",
"nl-NL": "merchandise",
"en-GB": "merchandise",
"ja-JP": "merchandise"
},
"tag_pk": 176
},
{
"name_loc": {
"en-US": "Over200",
"content_default": "Over200"
},
"tag_pk": 2248
},
{
"name_loc": {
"en-US": "Home",
"content_default": "Home",
"en-GB": "Home",
"es-MX": "casa",
"fr-CA": "maison"
},
"tag_pk": 21
},
{
"name_loc": {
"en-US": "Kitchen",
"content_default": "Kitchen",
"en-GB": "Kitchen",
"es-MX": "cocina",
"fr-CA": "cuisine"
},
"tag_pk": 23
}
],
"program_fk": 1,
"@timestamp": "2026-01-14T05:45:11.459675720Z",
"is_marketplace_plus": false,
"name_loc": {
"en-US": "Cashmere Coupe 16-Piece Dinnerware Set",
"content_default": "Cashmere Coupe 16-Piece Dinnerware Set",
"en-AU": "Cashmere Coupe 16-Piece Dinnerware Set",
"fr-FR": "Ensemble de vaisselle 16 pièces Cashmere Coupe",
"en-GB": "Cashmere Coupe 16-Piece Dinnerware Set",
"fr-CA": "Ensemble de vaisselle 16 pièces Cashmere Coupe"
},
"price": {
"price_points": 28999,
"variable_min_points": 28999,
"variable_max_points": 28999,
"point_range_group": [
{
"name": "20000-35000",
"range": {
"min": 20000,
"max": 35000
}
}
],
"promotion": {
"sale_percentage": 0,
"is_on_sale": false,
"old_price": 0
}
},
"catalog_type": "CATALOG",
"product_id": "MAX-CA-MAWBC188216",
"product_pk": 583666,
"description_loc": {
"en-US": "<p>THIS IS A MARKETPLACE SEARCH TEST!!!!</p>",
"content_default": "<p>THIS IS A MARKETPLACE SEARCH TEST!!!!</p>",
"en-AU": "<div><div>Add a little sophistication to your dinner table, with the timeless style of the Cashmere Coupe Dinner Set 16 Piece in white, from Maxwell & Williams. Made from fine bone china, so every dining occasion will be as luxurious as the next.</div></div><div><div>16-piece set.</div><div>Material: Fine Bone China.</div><div>Microwave, Oven, Dishwasher & Freezer Safe.</div></div>",
"fr-FR": "<div>Ajoutez un peu de sophistication à votre table avec le style intemporel de l'ensemble de vaisselle en cachemire Coupé 16 pièces en blanc, de Maxwell & Williams. Fabriqué en porcelaine d'os fin, ainsi chaque occasion de dîner sera aussi luxueuse que la suivante.<br></div><div><div>Ensemble de 16 pièces.</div><div>Matériau : porcelaine tendre.</div><div>Va au micro-ondes, au four, au lave-vaisselle et au congélateur.</div></div>",
"en-GB": "<div><div>Add a little sophistication to your dinner table, with the timeless style of the Cashmere Coupe Dinner Set 16 Piece in white, from Maxwell & Williams. Made from fine bone china, so every dining occasion will be as luxurious as the next.</div></div><div><div>16-piece set.</div><div>Material: Fine Bone China.</div><div>Microwave, Oven, Dishwasher & Freezer Safe.</div></div>",
"fr-CA": "<div>Ajoutez un peu de sophistication à votre table avec le style intemporel de l'ensemble de vaisselle en cachemire Coupé 16 pièces en blanc, de Maxwell & Williams. Fabriqué en porcelaine d'os fin, ainsi chaque occasion de dîner sera aussi luxueuse que la suivante.<br></div><div><div>Ensemble de 16 pièces.</div><div>Matériau : porcelaine tendre.</div><div>Va au micro-ondes, au four, au lave-vaisselle et au congélateur.</div></div>"
},
"product_date_created": "2020-02-27T16:41:22Z",
"brand": {
"name_loc": {
"content_default": "Maxwell & Williams",
"legacy_brand_tag_name": "Maxwell___Williams"
},
"brand_pk": 1402
},
"region_code": "CA"
}
},

As for the index definition, that’s in golang:

    properties := KeyValue{
        "brand": KeyValue{
           "properties": KeyValue{
             "brand_pk": KeyValue{
               "type": "long",
             },
           "name_loc": KeyValue{
             "properties": getLocaleProperties(locales, KEYWORD_ANALYZER, true),
           },
         },
        },
...

const KEYWORD_ANALYZER = "custom_keyword"

func getLocaleProperties(locales []string, analyzerName string, hasDefault bool) KeyValue {
	properties := make(KeyValue)
	for _, locale := range locales {
		key := fmt.Sprintf("content_%s", strings.ToLower(locale))
		properties[key] = KeyValue{
			"type":     "text",
			"analyzer": analyzerName,
		}
	}
	if hasDefault {
		properties["content_default"] = KeyValue{
			"type":     "text",
			"analyzer": analyzerName,
		}
	}

	return properties
}

Please let me know, if anything else is needed.

1 Like
4

Hello @Phil_McLachlan

Tried with below data & i also receive the same error message as you have shared :

PUT repro_brand_error_index
{
  "mappings": {
    "properties": {
      "region_code": {
        "type": "keyword"
      },
      "catalog_type": {
        "type": "keyword"
      },
      "brand": {
        "properties": {
          "brand_pk": {
            "type": "long"
          },
          "name_loc": {
            "properties": {
              "content_default": {
                "type": "text",
                "fields": {
                  "keyword": {
                    "type": "keyword"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

POST repro_brand_error_index/_doc
{
  "region_code": "US",
  "catalog_type": "CATALOG",
  "brand": {
    "brand_pk": 1402,
    "name_loc": {
      "content_default": "Maxwell & Williams"
    }
  }
}


POST _scripts/BRANDS_FILTER_REPRO
{
  "script": {
    "lang": "mustache",
    "source": "{\"query\":{\"bool\":{\"must\": [{\"match\":{\"region_code.keyword\":\"{{regionCode}}\"}},{\"match\":{\"catalog_type.keyword\":\"{{catalogType}}\"}},{\"multi_match\":{\"query\":\"{{keyword}}\",\"fuzziness\":\"AUTO:3,8\",\"prefix_length\":2,\"type\":\"best_fields\",\"operator\":\"and\",\"fields\": [\"brand.name_loc.content_default^3.1\"]}}]}},\"aggs\": {\"unique_brand_ids\": {\"terms\": {\"field\": \"brand.brand_pk\",\"size\": {{size}},\"order\": {\"brand_name_sort\": \"asc\"}}},\"brand_name_sort\": {\"terms\": {\"field\": \"brand.name_loc.content_default.keyword\",\"order\": {\"_key\": \"asc\"}}}},\"from\": {{from}},\"size\": 0}"
  }
}


GET repro_brand_error_index/_search/template
{
  "id": "BRANDS_FILTER_REPRO",
  "params": {
    "keyword": "maxwell",
    "regionCode": "US",
    "catalogType": "CATALOG",
    "fuzziness": true,
    "from": 0,
    "size": 100,
    "sort": "\"brand.name_loc.content_default.keyword\""
  }
}

Error:

"root_cause": [
      {
        "type": "invalid_path",
        "reason": "Invalid aggregation order path [brand_name_sort]. The provided aggregation [brand_name_sort] either does not exist, or is a pipeline aggregation and cannot be used to sort the buckets."
      }
    ]

I was not able to find the solution but have below documentation if it can help to find the solution :

As per your script, the unique_brand_ids terms aggregation attempts to order its buckets using brand_name_sort. However, brand_name_sort is defined as a sibling bucket (terms) aggregation, and Elasticsearch only allows ordering a terms aggregation by _key, _count, or by a metric sub-aggregation. As a result, Elasticsearch rejects brand_name_sort as an invalid order path.

Thanks!!

5

Thanks for your investigation. I guess you cannot have both a unique set of ids and sort it by another text field. I would have thought it was possible. One solution is to make another brands index per program. Probably a better solution is to sort in the microservice after calling the search template. There aren’t too many brands, so it should be ok performance wise.