[Recipes] SQL-Like Joining in Elasticsearch (Multiple and Multi-level Relations)

Problem:

Need to define multiple children for a parent. Need to define multi-level parent child relationship, and query for parent / grand parent supplying details of the child.

Solution Summary:

You can define multiple and multi-level relations within the join field. For understanding basic join relations, please refer here.

We will expand our hackathon example as follows: The "employer" parent will has two children: "team" and "organizer". The "team" will have another child "employee".

Prerequisites:

Working Elasticsearch cluster and Kibana.

If index hackathon_with_teams exist, delete it:

DELETE hackathon_with_teams

Solution Steps:

Create Mapping with Miltiple and Multi-level Relations

PUT hackathon_with_teams
{
"mappings": {
"_doc": {
"properties": {
"employer_to_employee": {
"type": "join",
"relations": {
"employer": ["team", "organizer"],
"team": "employee"
}
}
}
}
}
}

Note: "employer" has two children: "team" and "organizer". The "team" has one child "employee".

Adding Documents

Add parent (employer):

PUT hackathon_with_teams/_doc/1
{
"name": "company1",
"employer_to_employee": {
"name" : "employer"
}
}

Add team (child to employer & parent to employee):

PUT hackathon_with_teams/_doc/2?routing=1
{
"name": "Cloudericks",
"employer_to_employee": {
"name" : "team",
"parent" : "1"
}
}

Add employee (child to team & grandchild to employer):

PUT hackathon_with_teams/_doc/3?routing=1
{
"name": "Heartin",
"employer_to_employee": {
"name" : "employee",
"parent" : "2"
}
}

PUT hackathon_with_teams/_doc/4?routing=1
{
"name": "Krishnakumar",
"employer_to_employee": {
"name" : "employee",
"parent" : "2"
}
}

Multi-level query for child

Find all employees in company1:

GET hackathon_with_teams/_doc/_search
{
"query": {
"has_parent": {
"parent_type": "team",
"query": {
"has_parent": {
"parent_type": "employer",
"query": {
"match": {
"name": "company1"
}
}
}
}
}
}
}

Multi-level query for parent

Find all employers with employee name "Heartin".

GET hackathon_with_teams/_doc/_search
{
"query": {
"has_child": {
"type": "team",
"query": {
"has_child": {
"type": "employee",
"query": {
"match": {
"name": "Heartin"
}
}
}
}
}
}
}

Note: See my name is unique. Just kidding... :)

Querying for immediate parents or children

Querying for immediate parents or children are done exactly as you see in previous recipe.

GET hackathon_with_teams/_doc/_search
{
"query": {
"has_parent": {
"parent_type": "employer",
"query": {
"match": {
"name": "company1"
}
}
}
}
}

GET hackathon_with_teams/_doc/_search
{
"query": {
"has_parent": {
"parent_type": "team",
"query": {
"match": {
"name": "Cloudericks"
}
}
}
}
}

GET hackathon_with_teams/_doc/_search
{
"query": {
"has_child": {
"type": "employee",
"query": {
"match": {
"name": "Heartin"
}
}
}
}
}