Search for documents matching all terms in a nested array Elasticsearch

Question

I am learning to use Elasticsearch as a basic recommender engine. My elasticsearch document contains records with nested entities as follows

PUT recs/user/1
{
  "name" : "Brad Pitt",
  "movies_liked": [
    {
      "name": "Forrest Gump",
      "score": 1
    },
    {
      "name": "Terminator",
      "score": 4
    },
    {
      "name": "Rambo",
      "score": 4
    },
    {
      "name": "Rocky",
      "score": 4
    },
    {
      "name": "Good Will Hunting",
      "score": 2
    }
  ]
}

PUT recs/user/2
{
  "name" : "Tom Cruise",
  "movies_liked": [
    {
      "name": "Forrest Gump",
      "score": 2
    },
    {
      "name": "Terminator",
      "score": 1
    },
    {
      "name": "Rocky IV",
      "score": 1
    },
    {
      "name": "Rocky",
      "score": 1
    },
    {
      "name": "Rocky II",
      "score": 1
    },
    {
      "name": "Predator",
      "score": 4
    }
  ]
}

I would like to search for users who specifically like "Forrest Gump","Terminator" and "Rambo".

I have used a nested query which currently looks like this

POST recs/user/_search
{
  "query": {
    "nested": {
      "path": "movies_liked",
      "query": {
        "terms": {
          "movies_liked.name": ["Forrest Gump","Terminator","Rambo"]

          }
        }

    }
  }
}

However when I execute this search, I expected to see only the first record which has all the required terms, but in the results I am getting both the records. In the second record the user clearly does not have "Rambo" in his liked list. I understand that this query is doing an "OR" operation with the given terms, How do I tweak this query to do an "AND" operation so that only the records having all the terms get matched?

Try the terms query set and specify a value for the minimum_should_match_field. — harishkb
– harishkb, Commented Feb 5, 2018 at 14:02
@harishkb Thanks for the quick reply, I'll check it out and update. — Mayur Rao
– Mayur Rao, Commented Feb 5, 2018 at 14:04
What version of elasticsearch are you using, what's the mapping of your index? — Alkis Kalogeris
– Alkis Kalogeris, Commented Feb 5, 2018 at 21:16
@alkis Elasticsearch 6.1 mapping : { "mappings": { "user": { "properties": { "name": { "type": "text", "analyzer": "standard", "search_analyzer": "standard" }, "movies_liked": { "type": "nested", "properties": { "name": { "type": "keyword" }, "score": { "type": "double" } } }, "required_matches": { "type": "long" } } } } } — Mayur Rao
– Mayur Rao, Commented Feb 6, 2018 at 7:27
Just an update terms query seems to be doing the same thing as OR, also the documentation for terms query seems sparse — Mayur Rao
– Mayur Rao, Commented Feb 6, 2018 at 7:29

Nikolay Vasiliev · Accepted Answer · 2018-02-10 09:09:23Z

How do I tweak this query to do an "AND" operation so that only the records having all the terms get matched?

By using a bool query:

POST recs/user/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "movies_liked",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "movies_liked.name": [
                        "Forrest Gump"
                      ]
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "movies_liked",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "movies_liked.name": [
                        "Terminator"
                      ]
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "nested": {
            "path": "movies_liked",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "movies_liked.name": [
                        "Rambo"
                      ]
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Note that bool wraps around several nested queries, not the other way around. It is important because the scope of a nested query is the nested document, because it basically a hidden separate object.

Hope that helps!

Collectives™ on Stack Overflow

Search for documents matching all terms in a nested array Elasticsearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related