I have below documents in ElasticSearch
[
{
"class": " Grade 1",
"subject": [
"Mathematics",
"German",
"Geometry",
"Arts",
"Physical Education"
],
"student": [
{
"name": "George",
"id": "ORT-823FT"
},
{
"name": "Travis",
"id": "ORT-873FT"
},
{
"name": "Scott",
"id": "ORT-883FT"
}
]
},
{
"class": " Grade 2",
"subject": [
"Mathematics",
"German",
"Geometry",
"French",
"Arts",
"Physical Education"
],
"student": [
{
"name": "Gibbs",
"id": "ORT-923DG"
},
{
"name": "Elizabeth",
"id": "ORT-973DG"
},
{
"name": "Michale",
"id": "ORT-983DG"
}
]
}
]
I need to fetch the document only when the student name and id are matching, for eg: if the student name is George and the id is ORT-823FT, then the first document should be returned. On the other hand if the student name is Gibbs and the id is ORT-923DG then second document must be returned.
The below query works for me but is there better way to write ?
{
"query": {
"bool": {
"should": [
{
"match": {
"student.id": "ORT-823FT"
}
},
{
"match": {
"student.name": "George"
}
}
]
, "minimum_should_match": 2
}
}
}
Updated
The mapping for student is as below, I have added "type": "nested", as explained in the document.
{
"student": {
"type": "nested",
"properties": {
"studentResidence": {
"properties": {
"residenceAddress": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"phoneNumber": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"parentEmail": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"studentParentRelationShip": {
"properties": {
"relationshipType": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"residenceAddress": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"comments": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"validFor": {
"properties": {
"endDateTime": {
"type": "date"
},
"startDateTime": {
"type": "date"
}
}
}
}
}
}
The corresponding query for the same is:
{
"query": {
"nested": {
"path": "student",
"query": {
"bool": {
"must": [
{
"match": {
"student.id": "ORT-823FT"
}},{
"match": {
"student.name": "George"
}
}
]
}
}
}
}
}
I am still getting an incorrect output. Not sure where I am going wrong.
CodePudding user response：
Based on your sample data, Below mapping should work, note I created nested only for student property and rest properties are normal.
{
"mappings": {
"properties": {
"class": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"student": {
"type": "nested", --> note this
"properties": {
"id": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
},
"name": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
},
"subject": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}
}
}
After that you index the sample documents that you provided in your question below one more sample to test it properly.
{
"class": " Grade 2",
"subject": [
"Mathematics",
"German",
"Geometry",
"French",
"Arts",
"Physical Education"
],
"student": [
{
"name": "Gibbs",
"id": "ORT-abc"
},
{
"name": "Elizabeth",
"id": "ORT-973DG"
},
{
"name": "Michale",
"id": "ORT-983DG"
},
{
"name": "XYZ",
"id": "ORT-923DG"
}
]
}
Now same search query that your provided will return the proper results.
{
"query": {
"nested": {
"path": "student",
"query": {
"bool": {
"must": [
{
"match": {
"student.id": "ORT-823FT"
}
},
{
"match": {
"student.name": "George"
}
}
]
}
}
}
}
}
SR
"hits": [
{
"_index": "nested_mapping_student",
"_id": "1",
"_score": 4.0313807,
"_source": {
"class": " Grade 1",
"subject": [
"Mathematics",
"German",
"Geometry",
"Arts",
"Physical Education"
],
"student": [
{
"name": "George",
"id": "ORT-823FT"
},
{
"name": "Travis",
"id": "ORT-873FT"
},
{
"name": "Scott",
"id": "ORT-883FT"
}
]
}
}
]
CodePudding user response：
what you did is not correct as you are not using the nested field to store your student information, hence relationship between id and name is lost, this is very well explained in this official example.
You can also try it by indexing below document
{
"class": " Grade 2",
"subject": [
"Mathematics",
"German",
"Geometry",
"French",
"Arts",
"Physical Education"
],
"student": [
{
"name": "Gibbs",
"id": "ORT-abc"
},
{
"name": "Elizabeth",
"id": "ORT-973DG"
},
{
"name": "Michale",
"id": "ORT-983DG"
},
{
"name": "XYZ",
"id": "ORT-923DG"
}
]
}
And see this is also coming in the query of Gibbs and id ORT-923DG which shouldn't be the case.
You need to use the nested query to get your expected results in all cases.