Skip to content

Instantly share code, notes, and snippets.

@VarunVats9
Last active February 29, 2020 17:44
Elasticsearch - join queries, has_parent, has_child
// 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