Nested object / aggregation, Get Variation with positiv stock values

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"
          }
        }
      ]
    }
  }
}

I think you query is fine!
I know it's a bit confusing, but what you see, is just the source of your documents, not the results of the nested query.
You should add : inner_hits: {} to see the nested objects filtered.

{
          "nested": {
            "query": {
              "bool": {
                "must_not": {
                  "match": {
                    "variations.stock": 0
                  }
                }
              }
            },
            "inner_hits": {},
            "path": "variations",
            "score_mode": "max"
          }
        }

Hi @klof,

I just want aggs values with positive Stock from the variations.
However he gives me all sizes from nestes_sizes with include stock = 0.

An example from the response:

 "nested_sizes": {
      "doc_count": 4,
      "sizes": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "L",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          },
          {
            "key": "M",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          },
          {
            "key": "S",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          },
          {
            "key": "XS",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          }
        ]
      }
    }

The whole response:

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 2.6739764,
    "hits": [
      {
        "_index": "products",
        "_type": "product",
        "_id": "1",
        "_score": 2.6739764,
        "_source": {
          "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": []
        },
        "inner_hits": {
          "variations": {
            "hits": {
              "total": 1,
              "max_score": 1,
              "hits": [
                {
                  "_index": "products",
                  "_type": "product",
                  "_id": "1",
                  "_nested": {
                    "field": "variations",
                    "offset": 1
                  },
                  "_score": 1,
                  "_source": {
                    "stock": 1,
                    "sale_price": "29.95",
                    "price": "29.95",
                    "color": "Grün",
                    "brand-slug": "only",
                    "brand-name": "Only",
                    "size": "M"
                  }
                }
              ]
            }
          }
        }
      }
    ]
  },
  "aggregations": {
    "nested_categories": {
      "doc_count": 7,
      "categories": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": 1367,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          },
          {
            "key": 1368,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          },
          {
            "key": 1407,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          },
          {
            "key": 1408,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          },
          {
            "key": 1413,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          },
          {
            "key": 1420,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          },
          {
            "key": 6980,
            "doc_count": 1,
            "reverse_nested_categories": {
              "doc_count": 1
            }
          }
        ]
      }
    },
    "nested_sizes": {
      "doc_count": 4,
      "sizes": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "L",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          },
          {
            "key": "M",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          },
          {
            "key": "S",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          },
          {
            "key": "XS",
            "doc_count": 1,
            "reverse_nested_sizes": {
              "doc_count": 1
            }
          }
        ]
      }
    },
    "nested_colors": {
      "doc_count": 4,
      "colors": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "Grün",
            "doc_count": 4,
            "reverse_nested_colors": {
              "doc_count": 1
            }
          }
        ]
      }
    },
    "prices": {
      "doc_count": 4,
      "max_price": {
        "value": 29.95
      },
      "min_price": {
        "value": 29.95
      }
    },
    "nested_brands": {
      "doc_count": 4,
      "brands": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "only",
            "doc_count": 4,
            "reverse_nested_brands": {
              "doc_count": 1
            }
          }
        ]
      }
    }
  }
}

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.