Elastic query "must" does not work correctly

68 Views Asked by At

I have a problem with Elasticsearch I want to create a query that will find students in a class with certain skills. For this, I want to use a query with "AND". My query only works correctly if I specify only one skill eg:

{
  query: {
    bool: {
      must: [
        { term: { class_id: 15 } }
      ],
      filter: [
        {
          nested: {
            path: :hard_skills,
            query: {
              bool: {
                must: [
                  { match: { 'hard_skills.name': 'ruby' } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

If I enter more skills, for example 2:

{
  query: {
    bool: {
      must: [
        { term: { class_id: 15 } }
      ],
      filter: [
        {
          nested: {
            path: :hard_skills,
            query: {
              bool: {
                must: [
                  { match: { 'hard_skills.name': 'ruby' } },
                  { match: { 'hard_skills.name': 'rails' } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

then it does not find any pupils at all even though they exist.

Tried changing "match" to "term" which has the same effect for both cases:

{
  query: {
    bool: {
      must: [
        { term: { class_id: 15 } }
      ],
      filter: [
        {
          nested: {
            path: :hard_skills,
            query: {
              bool: {
                must: [
                  { term: { 'hard_skills.name': 'ruby' } },
                  { term: { 'hard_skills.name': 'rails' } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Then I tried using "terms". In this case, for several skills it works as "OR" instead of "AND" which finds pupils with one or more of the given skills:

{
  query: {
    bool: {
      must: [
        { term: { class_id: 15 } }
      ],
      filter: [
        {
          nested: {
            path: :hard_skills,
            query: {
              bool: {
                must: [
                 { terms: { "hard_skills.name": ["rails", "ruby"] } }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

I am using elasticsearch 5.6.5 my mapping:

"class_index"=>
  {"mappings"=>
    {"dynamic"=>"false",
     "properties"=>
      {"answers_included"=>{"type"=>"text", "analyzer"=>"keyword"},
       "candidate"=>
        {"type"=>"nested",
         "properties"=>
          {"about_me"=>{"type"=>"text", "copy_to"=>["chosen"]},
           "address"=>{"type"=>"nested", "properties"=>{"location"=>{"type"=>"geo_point"}}},
           "email"=>{"type"=>"text", "copy_to"=>["chosen"]},
           "first_name"=>{"type"=>"text", "copy_to"=>["chosen"]},
           "last_name"=>{"type"=>"text", "copy_to"=>["chosen"]},
           "phone_number"=>{"type"=>"text", "copy_to"=>["chosen"]}}},
       "hard_skills"=>{"type"=>"nested", "properties"=>{"name"=>{"type"=>"text", "analyzer"=>"keyword", "fielddata"=>true}}},
       "id"=>{"type"=>"integer"}

Please help me to figure out how to build the query to only find students who have both skills. Thank you in advance for your help

Data sample:
{
  "classes": [
    {
      "id": 1,
      "name": "Math Class"
    }
  ],
  "users": [
    {
      "id": 1,
      "name": "John",
      "last_name": "Doe",
      "class_id": 1,
      "hard_skills": [
        {
          "id": 1,
          "name": "ruby"
        },
        {
          "id": 2,
          "name": "rails"
        }
      ]
    },
    {
      "id": 2,
      "name": "Jane",
      "last_name": "Smith",
      "class_id": 1,
      "hard_skills": [
        {
          "id": 2,
          "name": "rails"
        }
      ]
    },
    {
      "id": 3,
      "name": "Bob",
      "last_name": "Johnson",
      "class_id": 1,
      "hard_skills": [
        {
          "id": 3,
          "name": "c"
        }
      ]
    }
  ],
  "hard_skills": [
    {
      "id": 1,
      "name": "rails"
    },
    {
      "id": 2,
      "name": "ruby"
    },
    {
      "id": 3,
      "name": "c"
    }
  ]
}
0

There are 0 best solutions below