Last active
February 29, 2020 17:44
Elasticsearch - join queries, has_parent, has_child
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Mapping | |
PUT /department >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"mappings": { | |
"properties": { | |
"join_field": { | |
"type": "join", | |
"relations": { | |
"department": "employee" | |
} | |
} | |
} | |
} | |
} | |
{ | |
"acknowledged" : true, | |
"shards_acknowledged" : true, | |
"index" : "department" | |
} | |
// Adding departments | |
PUT /department/_doc/1 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"name": "Development", | |
"join_field": "department" | |
} | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "1", | |
"_version" : 1, | |
"result" : "created", | |
"_shards" : { | |
"total" : 2, | |
"successful" : 2, | |
"failed" : 0 | |
}, | |
"_seq_no" : 0, | |
"_primary_term" : 1 | |
} | |
// Above query is a shortened version of this. | |
PUT /department/_doc/2 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"name": "Marketing", | |
"join_field": { | |
"name": "department" | |
} | |
} | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "2", | |
"_version" : 1, | |
"result" : "created", | |
"_shards" : { | |
"total" : 2, | |
"successful" : 2, | |
"failed" : 0 | |
}, | |
"_seq_no" : 1, | |
"_primary_term" : 1 | |
} | |
// Adding employees | |
// The routing value is mandatory because parent and child documents | |
// must be indexed on the same shard | |
PUT /department/_doc/3?routing=1 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"name": "Bo Andersen", | |
"age": 28, | |
"gender": "M", | |
"join_field": { | |
"name": "employee", | |
"parent": 1 | |
} | |
} | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "3", | |
"_version" : 1, | |
"result" : "created", | |
"_shards" : { | |
"total" : 2, | |
"successful" : 2, | |
"failed" : 0 | |
}, | |
"_seq_no" : 2, | |
"_primary_term" : 1 | |
} | |
PUT /department/_doc/4?routing=2 | |
{ | |
"name": "John Doe", | |
"age": 44, | |
"gender": "M", | |
"join_field": { | |
"name": "employee", | |
"parent": 2 | |
} | |
} | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "4", | |
"_version" : 1, | |
"result" : "created", | |
"_shards" : { | |
"total" : 2, | |
"successful" : 2, | |
"failed" : 0 | |
}, | |
"_seq_no" : 3, | |
"_primary_term" : 1 | |
} | |
PUT /department/_doc/5?routing=1 | |
{ | |
"name": "James Evans", | |
"age": 32, | |
"gender": "M", | |
"join_field": { | |
"name": "employee", | |
"parent": 1 | |
} | |
} | |
PUT /department/_doc/6?routing=1 | |
{ | |
"name": "Daniel Harris", | |
"age": 52, | |
"gender": "M", | |
"join_field": { | |
"name": "employee", | |
"parent": 1 | |
} | |
} | |
PUT /department/_doc/7?routing=2 | |
{ | |
"name": "Jane Park", | |
"age": 23, | |
"gender": "F", | |
"join_field": { | |
"name": "employee", | |
"parent": 2 | |
} | |
} | |
PUT /department/_doc/8?routing=1 | |
{ | |
"name": "Christina Parker", | |
"age": 29, | |
"gender": "F", | |
"join_field": { | |
"name": "employee", | |
"parent": 1 | |
} | |
} | |
// Querying by parent | |
// Given this parent id, and the type to be searched. | |
GET /department/_search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"query": { | |
"parent_id": { | |
"type": "employee", | |
"id": 1 | |
} | |
} | |
} | |
{ | |
"took" : 1, | |
"timed_out" : false, | |
"_shards" : { | |
"total" : 1, | |
"successful" : 1, | |
"skipped" : 0, | |
"failed" : 0 | |
}, | |
"hits" : { | |
"total" : { | |
"value" : 2, | |
"relation" : "eq" | |
}, | |
"max_score" : 0.53899646, | |
"hits" : [ | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "3", | |
"_score" : 0.53899646, | |
"_routing" : "1", | |
"_source" : { | |
"name" : "Bo Andersen", | |
"age" : 28, | |
"gender" : "M", | |
"join_field" : { | |
"name" : "employee", | |
"parent" : 1 | |
} | |
} | |
}, | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "5", | |
"_score" : 0.53899646, | |
"_routing" : "1", | |
"_source" : { | |
"name" : "James Evans", | |
"age" : 32, | |
"gender" : "M", | |
"join_field" : { | |
"name" : "employee", | |
"parent" : 1 | |
} | |
} | |
} | |
] | |
} | |
} | |
// Has_parent query | |
// Text can be searched via full text search, like match etc. | |
// Keywords can be used with term queries and aaggregation. | |
// Defult mapping creates field (text) and field.keyword (keyword) | |
GET /department/_search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"query": { | |
"has_parent": { | |
"parent_type": "department", | |
"query": { | |
"term": { | |
"name.keyword": "Development" | |
} | |
} | |
} | |
} | |
} | |
{ | |
"took" : 6, | |
"timed_out" : false, | |
"_shards" : { | |
"total" : 1, | |
"successful" : 1, | |
"skipped" : 0, | |
"failed" : 0 | |
}, | |
"hits" : { | |
"total" : { | |
"value" : 2, | |
"relation" : "eq" | |
}, | |
"max_score" : 1.0, | |
"hits" : [ | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "3", | |
"_score" : 1.0, | |
"_routing" : "1", | |
"_source" : { | |
"name" : "Bo Andersen", | |
"age" : 28, | |
"gender" : "M", | |
"join_field" : { | |
"name" : "employee", | |
"parent" : 1 | |
} | |
} | |
}, | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "5", | |
"_score" : 1.0, | |
"_routing" : "1", | |
"_source" : { | |
"name" : "James Evans", | |
"age" : 32, | |
"gender" : "M", | |
"join_field" : { | |
"name" : "employee", | |
"parent" : 1 | |
} | |
} | |
} | |
] | |
} | |
} | |
// Attach relevant parent's score also, default is 1.0 | |
GET /department/_search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"query": { | |
"has_parent": { | |
"parent_type": "department", | |
"score": true, | |
"query": { | |
"term": { | |
"name.keyword": "Development" | |
} | |
} | |
} | |
} | |
} | |
{ | |
"took" : 4, | |
"timed_out" : false, | |
"_shards" : { | |
"total" : 1, | |
"successful" : 1, | |
"skipped" : 0, | |
"failed" : 0 | |
}, | |
"hits" : { | |
"total" : { | |
"value" : 2, | |
"relation" : "eq" | |
}, | |
"max_score" : 1.3862942, | |
"hits" : [ | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "3", | |
"_score" : 1.3862942, | |
"_routing" : "1", | |
"_source" : { | |
"name" : "Bo Andersen", | |
"age" : 28, | |
"gender" : "M", | |
"join_field" : { | |
"name" : "employee", | |
"parent" : 1 | |
} | |
} | |
}, | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "5", | |
"_score" : 1.3862942, | |
"_routing" : "1", | |
"_source" : { | |
"name" : "James Evans", | |
"age" : 32, | |
"gender" : "M", | |
"join_field" : { | |
"name" : "employee", | |
"parent" : 1 | |
} | |
} | |
} | |
] | |
} | |
} | |
// has_child query | |
GET /department/_search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"query": { | |
"has_child": { | |
"type": "employee", | |
"query": { | |
"bool": { | |
"must": [ | |
{ | |
"range": { | |
"age": { | |
"lte": 50 | |
} | |
} | |
} | |
], | |
"should": [ | |
{ | |
"term": { | |
"gender.keyword": "M" | |
} | |
} | |
] | |
} | |
} | |
} | |
} | |
} | |
{ | |
"took" : 3, | |
"timed_out" : false, | |
"_shards" : { | |
"total" : 1, | |
"successful" : 1, | |
"skipped" : 0, | |
"failed" : 0 | |
}, | |
"hits" : { | |
"total" : { | |
"value" : 2, | |
"relation" : "eq" | |
}, | |
"max_score" : 1.0, | |
"hits" : [ | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "1", | |
"_score" : 1.0, | |
"_source" : { | |
"name" : "Development", | |
"join_field" : "department" | |
} | |
}, | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "2", | |
"_score" : 1.0, | |
"_source" : { | |
"name" : "Marketing", | |
"join_field" : "department" | |
} | |
} | |
] | |
} | |
} | |
// Relevance score with score_mode | |
GET /department/_search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"query": { | |
"has_child": { | |
"type": "employee", | |
"score_mode": "sum", | |
"query": { | |
"bool": { | |
"must": [ | |
{ | |
"range": { | |
"age": { | |
"gte": 10 | |
} | |
} | |
} | |
], | |
"should": [ | |
{ | |
"term": { | |
"gender.keyword": { | |
"value": "M" | |
} | |
} | |
} | |
] | |
} | |
} | |
} | |
} | |
} | |
{ | |
"took" : 1, | |
"timed_out" : false, | |
"_shards" : { | |
"total" : 1, | |
"successful" : 1, | |
"skipped" : 0, | |
"failed" : 0 | |
}, | |
"hits" : { | |
"total" : { | |
"value" : 2, | |
"relation" : "eq" | |
}, | |
"max_score" : 2.2670627, | |
"hits" : [ | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "1", | |
"_score" : 2.2670627, | |
"_source" : { | |
"name" : "Development", | |
"join_field" : "department" | |
} | |
}, | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "2", | |
"_score" : 1.1335313, | |
"_source" : { | |
"name" : "Marketing", | |
"join_field" : "department" | |
} | |
} | |
] | |
} | |
} | |
// Specifying min and max children | |
GET /department/_search >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> | |
{ | |
"query": { | |
"has_child": { | |
"type": "employee", | |
"score_mode": "sum", | |
"min_children": 2, | |
"max_children": 5, | |
"query": { | |
"bool": { | |
"must": [ | |
{ | |
"range": { | |
"age": { | |
"gte": 10 | |
} | |
} | |
} | |
], | |
"should": [ | |
{ | |
"term": { | |
"gender.keyword": { | |
"value": "M" | |
} | |
} | |
} | |
] | |
} | |
} | |
} | |
} | |
} | |
{ | |
"took" : 2, | |
"timed_out" : false, | |
"_shards" : { | |
"total" : 1, | |
"successful" : 1, | |
"skipped" : 0, | |
"failed" : 0 | |
}, | |
"hits" : { | |
"total" : { | |
"value" : 1, | |
"relation" : "eq" | |
}, | |
"max_score" : 2.2670627, | |
"hits" : [ | |
{ | |
"_index" : "department", | |
"_type" : "_doc", | |
"_id" : "1", | |
"_score" : 2.2670627, | |
"_source" : { | |
"name" : "Development", | |
"join_field" : "department" | |
} | |
} | |
] | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment