CODEWITHSUNDEEP
elasticsearchfilternestedaggregation
murb
murb

Reputation: 1860

Nested filtering in elasticsearch with more than one term of the same nested type

I'm new to elasticsearch, so maybe my approach is plain wrong, but I want to make an index of recipes and allow the user to filter it down with the aggregated ingredients that are still found in the subset.

Maybe I'm using the wrong language to explain so maybe this example will clarify. I would like to search for recipes with the term salt; which results in three recipes:

  1. with ingredients: salt, flour, water
  2. with ingredients: salt, pepper, egg
  3. with ingredients: water, flour, egg, salt

The aggregate on the results ingredients returns salt, flour, water, pepper, egg. When I filter with flour I only want recipe 1 and 3 to appear in the search results (and the aggregate on ingredients should only return salt, flour, water, egg and salt). When I add another filter egg I want only recipe 3 to appear (and the aggregate should only return water, flour, egg, salt).

I can't make the latter to work: one filter next to the default query does narrow down the results as desired but when adding the other term (egg) to the terms filter the results again start to include b as well, as if it were an OR filter. Adding AND however to the filter execution results in NO results ... what am I doing wrong?

My mapping:

{
  "recipe": {
    "properties": {
      "title": {
        "analyzer": "dutch",
        "type": "string"
      },
      "ingredients": {
        "type": "nested",
        "properties": {
          "name": {
            "type": "string",
            "analyzer": "dutch",
            "include_in_parent": true,
            "fields": {
              "raw": {
                "type": "string",
                "index": "not_analyzed"
              }
            }
          }
        }
      }
    }
  }
}

My query:

{
  "query": {
    "filtered": {
      "query": {
        "bool": {
          "should": [
            {
              "match": {
                "_all": "salt"
              }
            }
          ]
        }
      },
      "filter": {
        "nested": {
          "path": "ingredients",
          "filter": {
            "terms": {
              "ingredients.name": [
                "flour",
                "egg"
              ],
              "execution": "and"
            }
          }
        }
      }
    }
  },
  "size": 50,
  "aggregations": {
    "ingredients": {
      "nested": {
        "path": "ingredients"
      },
      "aggregations": {
        "count": {
          "terms": {
            "field": "ingredients.name.raw"
          }
        }
      }
    }
  }
}

Upvotes: 2

Views: 1340

Answers (1)

ThomasC
ThomasC

Reputation: 8165

Why are you using a nested mapping here? Its main purpose is to keep relations between the sub-object attributes, but your ingredients field has just one attribute and can be modeled simply as a string field.

So, if you update your mapping like this :

POST recipes
{
  "mappings": {
    "recipe": {
      "properties": {
        "title": {
          "type": "string"
        },
        "ingredients": {
          "name": {
            "type": "string",
            "fields": {
              "raw": {
                "type": "string",
                "index": "not_analyzed"
              }
            }
          }
        }
      }
    }
  }
}

You can still index your recipes as :

{
  "title":"recipe b",
  "ingredients":["salt","pepper","egg"]
}

And this query gives you the result you are waiting for :

POST recipes/recipe/_search
{
  "query": {
    "filtered": {
      "query": {
        "match": {
          "_all": "salt"
        }
      },
      "filter": {
        "terms": {
          "ingredients": [
            "flour",
            "egg"
          ],
          "execution": "and"
        }
      }
    }
  },
  "size": 50,
  "aggregations": {
    "ingredients": {
      "terms": {
        "field": "ingredients"
      }
    }
  }
}

which is :

{
   ...
   "hits": {
      "total": 1,
      "max_score": 0.22295055,
      "hits": [
         {
            "_index": "recipes",
            "_type": "recipe",
            "_id": "PP195TTsSOy-5OweArNsvA",
            "_score": 0.22295055,
            "_source": {
               "title": "recipe c",
               "ingredients": [
                  "salt",
                  "flour",
                  "egg",
                  "water"
               ]
            }
         }
      ]
   },
   "aggregations": {
      "ingredients": {
         "buckets": [
            {
               "key": "egg",
               "doc_count": 1
            },
            {
               "key": "flour",
               "doc_count": 1
            },
            {
               "key": "salt",
               "doc_count": 1
            },
            {
               "key": "water",
               "doc_count": 1
            }
         ]
      }
   }
}

Hope this helps.

Upvotes: 2

Related Questions