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

Guillaume_R · November 25, 2020, 11:57pm

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

Guillaume_R · November 26, 2020, 12:20am

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

egalpin · November 26, 2020, 9:40pm

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.

Guillaume_R · November 26, 2020, 10:28pm

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)

egalpin:

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": {
                          "..."
                 }

egalpin · November 27, 2020, 6:38pm

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

egalpin · November 27, 2020, 6:39pm

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

Guillaume_R · November 27, 2020, 6:53pm

Thank you so much. This exactly the expected result !

system · December 25, 2020, 6:53pm

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