Elasticsearch version:
6.2
Good day,
we are not moving forward with nested object and aggregation.
In the example below, there are variations as nested.
We only want values with positive stock in the aggregations.
How can we solve this best?
PUT products {
"mappings": {
"product": {
"properties": {
"categories": {
"type": "nested",
"properties": {
"id": {
"type": "integer",
"index": false
},
"name": {
"type": "keyword"
},
"slug": {
"type": "keyword"
}
}
},
"images": {
"type": "text",
"index": false
},
"index": {
"properties": {
"_id": {
"type": "long"
},
"_index": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"_type": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"name": {
"type": "text"
},
"permalink": {
"type": "text",
"index": false
},
"related_products": {
"properties": {
"first_image": {
"type": "text",
"index": false
},
"permalink": {
"type": "text",
"index": false
}
}
},
"variations": {
"type": "nested",
"properties": {
"brand-name": {
"type": "keyword"
},
"brand-slug": {
"type": "keyword"
},
"color": {
"type": "keyword"
},
"price": {
"type": "double"
},
"sale_price": {
"type": "double"
},
"size": {
"type": "keyword"
},
"stock": {
"type": "integer"
}
}
}
}
}
}
}
PUT products/product/1 {
"name": "Only - SAMANTHA S/S PULLOVER KNT BB - Grün (HONEYDEW)",
"permalink": "/only-samantha-s-s-pullover-knt-bb-gruen-honeydew/",
"categories": [
{
"id": 1368,
"name": "Bekleidung",
"slug": "bekleidung"
},
{
"id": 1367,
"name": "Damen",
"slug": "damen"
},
{
"id": 1413,
"name": "Kurzarmpullover",
"slug": "kurzarmpullover"
},
{
"id": 6980,
"name": "Only",
"slug": "only"
},
{
"id": 1408,
"name": "Pullover",
"slug": "pullover"
},
{
"id": 1407,
"name": "Pullover & Strickjacken",
"slug": "pullover-strickjacken"
},
{
"id": 1420,
"name": "Rundhalspullover",
"slug": "rundhalspullover"
}
],
"images": [
"/wp-content/uploads/2018/05/87cc3bb4884582ba6030cdcb80def112.jpg",
"/wp-content/uploads/2018/05/2a05ce66c0ce35086c85357f8439a935.jpg"
],
"variations": [
{
"stock": 0,
"sale_price": "29.95",
"price": "29.95",
"color": "Grün",
"brand-slug": "only",
"brand-name": "Only",
"size": "L"
},
{
"stock": 1,
"sale_price": "29.95",
"price": "29.95",
"color": "Grün",
"brand-slug": "only",
"brand-name": "Only",
"size": "M"
},
{
"stock": 0,
"sale_price": "29.95",
"price": "29.95",
"color": "Grün",
"brand-slug": "only",
"brand-name": "Only",
"size": "S"
},
{
"stock": 0,
"sale_price": "29.95",
"price": "29.95",
"color": "Grün",
"brand-slug": "only",
"brand-name": "Only",
"size": "XS"
}
],
"related_products": []
}
GET products/_search {
"from": 0,
"size": 800,
"aggs": {
"prices": {
"aggs": {
"min_price": {
"min": {
"field": "variations.price"
}
},
"max_price": {
"max": {
"field": "variations.price"
}
}
},
"nested": {
"path": "variations"
}
},
"nested_colors": {
"nested": {
"path": "variations"
},
"aggs": {
"colors": {
"terms": {
"field": "variations.color",
"size": 10000,
"order": [
{
"_term": "asc"
}
]
},
"aggs": {
"reverse_nested_colors": {
"reverse_nested": {}
}
}
}
}
},
"nested_sizes": {
"nested": {
"path": "variations"
},
"aggs": {
"sizes": {
"terms": {
"field": "variations.size",
"size": 10000
},
"aggs": {
"reverse_nested_sizes": {
"reverse_nested": {}
}
}
}
}
},
"nested_categories": {
"nested": {
"path": "categories"
},
"aggs": {
"categories": {
"terms": {
"field": "categories.id",
"size": 10000
},
"aggs": {
"reverse_nested_categories": {
"reverse_nested": {}
}
}
}
}
},
"nested_brands": {
"nested": {
"path": "variations"
},
"aggs": {
"brands": {
"terms": {
"field": "variations.brand-slug",
"size": 10000
},
"aggs": {
"reverse_nested_brands": {
"reverse_nested": {}
}
}
}
}
}
},
"query": {
"bool": {
"must": [
{
"nested": {
"query": {
"bool": {
"must_not": {
"match": {
"variations.stock": 0
}
}
}
},
"path": "variations",
"score_mode": "max"
}
},
{
"nested": {
"query": {
"bool": {
"must": [
{
"match": {
"categories.slug": "damen"
}
}
]
}
},
"path": "categories"
}
}
]
}
}
}