So I have a set of indexed products that contains a dictionary with a single key and a list of values, with which I'm trying to build a facetted search. However I am very much an elastic newbie.
Product Product {
Dictionary<string, List<string>> Properties
//extra fields removed for simplicity
}
where properties may be something along the lines of
["Color":["Blue","Yellow","Red"],"Size":["Small","Medium","Large"]
or
["Material":["Wood"], "Shape":["Circle","Square"], "Size":["Tiny","Medium","Large","Huge"]
I'm looking to write a couple of aggregations, which will return the keys, and the values of those keys.
I.e if the examples above were to be indexed, the first aggregation would return a bucket containing "Color","Size","Material","Shape"
and the second aggregation would return 4 buckets, each with the unique values for each key.
i.e Size:["Tiny","small","medium","large","huge"]
etc
I realize I need a nested aggregation for this, however none of my attempts are bringing back anything in the buckets. Any pointers would be greatly appreciated. Here's what I have so far.
var ProductsQuery = client.Search<Product>(s => s
.Index("products")
.Query(q => q.MatchAll())
.Aggregations(a => a
.Nested("properties", n => n
.Path(p => p.Properties.Suffix("keyword"))
.Aggregations(a => a
.Terms("property-keys", t => t
.Field(f => f.Properties.Keys.Suffix("keyword"))))));
Edit for some requested details:
The current properties mapping (It appears to be creating a new mapping for every Key which I'm not sure if that's typical or not?) I haven't put the whole object mapping here as it's rather huge. Products have a lot of fields:
"properties" : {
"properties" : {
"Colour" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Equipment" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Football Age" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Football Size" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Frame Weight" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Garment" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Head Shape" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Level" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Product" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Size" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Sport" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Surface" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Type" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Unit" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"Weight" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"comparer" : {
"type" : "object"
},
"count" : {
"type" : "integer"
},
"item" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"keys" : {
"properties" : {
"count" : {
"type" : "integer"
}
}
},
"values" : {
"properties" : {
"count" : {
"type" : "integer"
}
}
}
}
}
and some indexed documents
"hits" : [
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134550",
"_score" : 1.0,
"_source" : {
"properties" : {
"Type" : [
"Sleds"
],
"Product" : [
"Sleds"
],
"Colour" : [
"Black"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134566",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Fitness"
],
"Type" : [
"Corner",
"Edge",
"Middle"
],
"Size" : [
"10mm",
"15mm",
"20mm"
],
"Product" : [
"Floor Matting"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134576",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Rugby"
],
"Type" : [
"Skills Training"
],
"Equipment" : [
"Rugby Balls"
],
"Size" : [
"4",
"5"
],
"Level" : [
"Skills"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134579",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Rugby"
],
"Type" : [
"Match Union"
],
"Equipment" : [
"Rugby Balls"
],
"Size" : [
"4",
"5"
],
"Level" : [
"Club",
" School"
],
"Unit" : [
"12 Pack",
"Each"
],
"Colour" : [
"Blue",
"Red",
"White"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134600",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Rugby"
],
"Size" : [
"Large",
"Medium",
"Small",
"X/Large",
"X/Small",
"XX/Small",
"XXX/Small"
],
"Garment" : [
"Gloves"
],
"Colour" : [
"Red"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134601",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Netball"
],
"Size" : [
"Large",
"X/Large",
"X/Small",
"XX/Small",
"XXX/Small"
],
"Garment" : [
"Gloves"
],
"Colour" : [
"Red"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134609",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Netball"
],
"Size" : [
"Large",
"Medium",
"Small",
"X/Large",
"X/Small",
"XXX/Small"
],
"Garment" : [
"Gloves"
],
"Colour" : [
"Black",
"Green"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134617",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Football"
],
"Type" : [
"Training"
],
"Football Size" : [
"2"
],
"Equipment" : [
"Footballs"
],
"Size" : [
"4",
"5"
],
"Unit" : [
"12 Pack",
"Each"
],
"Weight" : [
"290",
"360"
],
"Surface" : [
"Grass",
" Astroturf"
],
"Football Age" : [
"9-14 years",
" 14+ years"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134548",
"_score" : 1.0,
"_source" : {
"properties" : {
"Type" : [
"Sleds"
],
"Product" : [
"Sleds"
],
"Colour" : [
"Black",
"Grey"
]
}
}
},
{
"_index" : "products-20-01-2021-13-49-08",
"_type" : "_doc",
"_id" : "134558",
"_score" : 1.0,
"_source" : {
"properties" : {
"Sport" : [
"Squash"
],
"Equipment" : [
"Squash Rackets"
],
"Size" : [
"27\""
],
"Head Shape" : [
"Bridged Closed Throat"
],
"Frame Weight" : [
"Over 160g"
]
}
}
}
]
First off, in order to obtain these buckets you could say with Query DSL the following:
POST products-*/_search
{
"size": 0,
"aggs": {
"by_Colour": {
"terms": {
"field": "properties.Colour.keyword"
}
},
"by_Size": {
"terms": {
"field": "properties.Size.keyword"
}
}
}
}
which would then need to be translated into the NEST code -- and I'm sure there are plenty of examples out there.
But your observation was correct -- ES created lots of mappings automatically. I'd advise against the current data format and rather go with:
{
"properties": [
{
"key": "Type",
"values": ["Sleds"]
},
{
"key": "Product",
"values": ["Sleds"]
},
{
"key": "Colour",
"values": ["Black"]
}
]
}
and making properties of type nested.
That way you'll query on shared paths properties.key and aggregate on properties.values.keyword.
Keep in mind that nested field types need their own, actual nested mapping -- you cannot use nested queries without the proper mappings.
Check this related question and also this answer for more context.