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.