Last active
February 29, 2020 17:44
-
-
Save VarunVats9/45944e9667e5d57872ccf6231d00f037 to your computer and use it in GitHub Desktop.
Elasticsearch - join queries, has_parent, has_child
This file contains hidden or 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