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