Filter Aggregation on nested object ... not working as expected!

Hello,
I have some product like this

{
          "id_product" : "180",
          "name" : "Domane AL 3 femme",
          "manufacturer" : "TREK",
          "cat_path" : "Product>377~velos~Vélos>35~route~Route>44~course~Course",
          "variants" : [
            {
              "id_product_attribute" : "140601",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "56",
              "color" : "PACIFIC",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "140602",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "54",
              "color" : "PACIFIC",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "140603",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "52",
              "color" : "PACIFIC",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "140604",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "50",
              "color" : "PACIFIC",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "140605",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "47",
              "color" : "PACIFIC",
              "warehouse" : [
                "Web",
                "Lausanne"
              ],
              "stock" : 1,
              "stock_magasin" : 1
            },
            {
              "id_product_attribute" : "142249",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "47",
              "color" : "BLANC CRISTAL",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "142250",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "50",
              "color" : "BLANC CRISTAL",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "142251",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "52",
              "color" : "BLANC CRISTAL",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "142252",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "54",
              "color" : "BLANC CRISTAL",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            },
            {
              "id_product_attribute" : "142253",
              "manufacturer" : "TREK",
              "gender" : [
                "Femme"
              ],
              "collection" : "2020",
              "offer" : "Actions",
              "size" : "56",
              "color" : "BLANC CRISTAL",
              "warehouse" : [ ],
              "stock" : 0,
              "stock_magasin" : 0
            }
          ],
          "stock" : 1
        }

I have a query to retrieve aggs ... and I don't understand the result. Here is the POST for the agg.

POST prestashop_ch/_search
{
  "size": 10,
  "aggs": {
    "agg_size": {
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "cat_path.tree": "Product>377~velos~Vélos>35~route~Route>44~course~Course"
              }
            },
            {
              "nested": {
                "path": "variants",
                "query": {
                  "bool": {
                    "must": [
                      {
                        "terms": {
                          "variants.gender": [
                            "Femme"
                          ]
                        }
                      },
                      {
                        "terms": {
                          "variants.manufacturer": [
                            "TREK"
                          ]
                        }
                      },
                      {
                        "term": {
                          "variants.warehouse": "Lausanne"
                        }
                      }
                    ]
                  }
                }
              }
            }
          ]
        }
      },
      "aggs": {
        "facets": {
          "nested": {
            "path": "variants"
          },
          "aggs": {
            "values": {
              "terms": {
                "field": "variants.size"
              },
              "aggs": {
                "top_reverse_nested": {
                  "reverse_nested": {}
                }
              }
            }
          }
        }
      }
    }
  },
  "post_filter": {
    "bool": {
      "must": [
        {
          "term": {
            "cat_path.tree": "Product>377~velos~Vélos>35~route~Route>44~course~Course"
          }
        },
        {
          "nested": {
            "path": "variants",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "variants.gender": [
                        "Femme"
                      ]
                    }
                  },
                  {
                    "terms": {
                      "variants.manufacturer": [
                        "TREK"
                      ]
                    }
                  },
                  {
                    "terms": {
                      "variants.warehouse": [
                        "Lausanne"
                      ]
                    }
                  },
                  {
                    "terms": {
                      "variants.size": [
                        "47"
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
} 

And the result

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "aggregations" : {
    "agg_size" : {
      "doc_count" : 1,
      "facets" : {
        "doc_count" : 10,
        "values" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : "47",
              "doc_count" : 2,
              "top_reverse_nested" : {
                "doc_count" : 1
              }
            },
            {
              "key" : "50",
              "doc_count" : 2,
              "top_reverse_nested" : {
                "doc_count" : 1
              }
            },
            {
              "key" : "52",
              "doc_count" : 2,
              "top_reverse_nested" : {
                "doc_count" : 1
              }
            },
            {
              "key" : "54",
              "doc_count" : 2,
              "top_reverse_nested" : {
                "doc_count" : 1
              }
            },
            {
              "key" : "56",
              "doc_count" : 2,
              "top_reverse_nested" : {
                "doc_count" : 1
              }
            }
          ]
        }
      }
    }
  }
}

I don't understand why I have other size than the "47" as this is the only one that match the filter.
How can I solve that ? (the problem is that if I display this agg and the user select the size "50" i makes of course no result.

Thanks a lot

By the way, here is the mapping and setting ...

PUT prestashop_ch?pretty
{
  "settings": {
    "number_of_shards": 1,
    "number_of_replicas": 0,
    "analysis": {
      "analyzer": {
        "custom_path_tree": {
          "tokenizer": "custom_hierarchy"
        },
        "custom_path_tree_reversed": {
          "tokenizer": "custom_hierarchy_reversed"
        },
        "final": {
          "type": "custom",
          "tokenizer": "keyword",
          "filter": "lowercase"
        }
      },
      "tokenizer": {
        "custom_hierarchy": {
          "type": "path_hierarchy",
          "delimiter": ">"
        },
        "custom_hierarchy_reversed": {
          "type": "path_hierarchy",
          "delimiter": ">",
          "reverse": "true"
        }
      }
    }
  },
  "mappings": {
    "_doc": {
      "properties": {
        "id_product": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "link_rewrite": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "cat_path": {
          "type": "text",
          "fields": {
            "tree": {
              "type": "text",
              "analyzer": "custom_path_tree",
              "fielddata": true,
              "search_analyzer": "keyword"
            },
            "tree_reversed": {
              "type": "text",
              "analyzer": "custom_path_tree_reversed",
              "fielddata": true,
              "search_analyzer": "keyword"
            }
          }
        },
        "manufacturer": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "name": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "reference": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword",
              "ignore_above": 256
            }
          }
        },
        "variants": {
          "type": "nested",
          "properties": {
            
            "stock": {
              "type": "long"
            },
            "stock_magasin": {
              "type": "long"
            },
            "size": {
              "type": "keyword"
              }
              ,
            "collection": {
              "type": "keyword"
              }
              ,
            "manufacturer": {
              "type": "keyword"
              }
              ,
            "color": {
              "type": "keyword"
              }
              ,
            "gender": {
              "type": "keyword"
              }
               ,
            "offer": {
              "type": "keyword"
              }
               ,
            "warehouse": {
              "type": "keyword"
              }
              
          }
        }
      }
    }
  }
}

What's happening here is that the agg filters are running against parent documents i.e. the filters are not within a nested aggregation (even though the must clauses themselves are in a nested context). So your filter aggregation roughly translates to "match all parent documents that have at least one nested variants document with manufacturer TREK, warehouse Lausanne etc".

When you then enter a nested context via your agg named facets, a "fan-out" occurs. All the parent docs which matched the bool filter "explode" to include all nested documents attached to those parent documents (regardless of whether or not the nested documents matched your initial bool filter). You can prove this by observing the doc_count under agg_size compared to the doc_count under facets. You could further prove it by adding a terms aggregation on the _id field under the top_reverse_nested agg. I believe it would show the same value for all aggs.

If you want to only have buckets in facets which match your filters, I suggest moving the filters into the nested agg context which facets creates. This will filter the nested docs themselves and not the parent docs to which the nested docs belong. When you move the filters into the nested facets agg, you'll also need to remove the nested portion of your bool clause. This part:

 {
              "nested": {
                "path": "variants",
                "query": {
                          "..."
                 }

Note also that post_filter applies to hits and not aggs. There is no size filter in the aggs in the query you posted. I'm not sure if that was intentional or not.

Hi egalpin,
Thanks a lot for your explanation. I'm not sure to see how to do that. Could you please help ? The query part (for the post_filter it return exactly the expected result)

@Guillaume_R Here's a query that will produce aggs which only include the size 47. Admittedly, I'm unclear on the goal of this query, but I believe it does what you ask:

{
    "size": 10,
    "aggs": {
        "agg_size": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "term": {
                                "cat_path.tree": "Product>377~velos~Vélos>35~route~Route>44~course~Course"
                            }
                        }
                    ]
                }
            },
            "aggs": {
                "facets": {
                    "nested": {
                        "path": "variants"
                    },
                    "aggs": {
                        "my_nested_filters": {
                            "filter": {
                                "bool": {
                                    "must": [
                                        {
                                            "terms": {
                                                "variants.gender": [
                                                    "Femme"
                                                ]
                                            }
                                        },
                                        {
                                            "terms": {
                                                "variants.manufacturer": [
                                                    "TREK"
                                                ]
                                            }
                                        },
                                        {
                                            "term": {
                                                "variants.warehouse": "Lausanne"
                                            }
                                        }
                                    ]
                                }
                            },
                            "aggs": {
                                "values": {
                                    "terms": {
                                        "field": "variants.size"
                                    },
                                    "aggs": {
                                        "top_reverse_nested": {
                                            "reverse_nested": {}
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    "post_filter": {
        "bool": {
            "must": [
                {
                    "term": {
                        "cat_path.tree": "Product>377~velos~Vélos>35~route~Route>44~course~Course"
                    }
                },
                {
                    "nested": {
                        "path": "variants",
                        "query": {
                            "bool": {
                                "must": [
                                    {
                                        "terms": {
                                            "variants.gender": [
                                                "Femme"
                                            ]
                                        }
                                    },
                                    {
                                        "terms": {
                                            "variants.manufacturer": [
                                                "TREK"
                                            ]
                                        }
                                    },
                                    {
                                        "terms": {
                                            "variants.warehouse": [
                                                "Lausanne"
                                            ]
                                        }
                                    },
                                    {
                                        "terms": {
                                            "variants.size": [
                                                "47"
                                            ]
                                        }
                                    }
                                ]
                            }
                        }
                    }
                }
            ]
        }
    }
}

Response to my above query

{
    "took": 57,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 1,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [
            {
                "_index": "foobar",
                "_type": "_doc",
                "_id": "1",
                "_score": 1.0,
                "_source": {
                    "id_product": "180",
                    "name": "Domane AL 3 femme",
                    "manufacturer": "TREK",
                    "cat_path": "Product>377~velos~Vélos>35~route~Route>44~course~Course",
                    "variants": [
                        {
                            "id_product_attribute": "140601",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "56",
                            "color": "PACIFIC",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "140602",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "54",
                            "color": "PACIFIC",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "140603",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "52",
                            "color": "PACIFIC",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "140604",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "50",
                            "color": "PACIFIC",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "140605",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "47",
                            "color": "PACIFIC",
                            "warehouse": [
                                "Web",
                                "Lausanne"
                            ],
                            "stock": 1,
                            "stock_magasin": 1
                        },
                        {
                            "id_product_attribute": "142249",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "47",
                            "color": "BLANC CRISTAL",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "142250",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "50",
                            "color": "BLANC CRISTAL",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "142251",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "52",
                            "color": "BLANC CRISTAL",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "142252",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "54",
                            "color": "BLANC CRISTAL",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        },
                        {
                            "id_product_attribute": "142253",
                            "manufacturer": "TREK",
                            "gender": [
                                "Femme"
                            ],
                            "collection": "2020",
                            "offer": "Actions",
                            "size": "56",
                            "color": "BLANC CRISTAL",
                            "warehouse": [],
                            "stock": 0,
                            "stock_magasin": 0
                        }
                    ],
                    "stock": 1
                }
            }
        ]
    },
    "aggregations": {
        "agg_size": {
            "doc_count": 1,
            "facets": {
                "doc_count": 10,
                "my_nested_filters": {
                    "doc_count": 1,
                    "values": {
                        "doc_count_error_upper_bound": 0,
                        "sum_other_doc_count": 0,
                        "buckets": [
                            {
                                "key": "47",
                                "doc_count": 1,
                                "top_reverse_nested": {
                                    "doc_count": 1
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
}

Thank you so much. This exactly the expected result !
:+1:

1 Like

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