[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"
        }
      }
    }
  }
}

Contact

Please first use the contact form or facebook page messaging to connect.

Offline Contact
We currently connect locally for discussions and sessions at Bangalore, India. Please follow us on our facebook page for details.
WhatsApp (Primary): (+91) 7411174113
Phone (Escalations): (+91) 7411174114

Business newsletter

Complete the form below, and we'll send you an e-mail every now and again with all the latest news.

About

CloudMaterials.com is my blog to share notes and learning materials on Cloud and Data Analytics. My current focus is on Amazon Web Services.

I like to write. I try to document what I learn and share with others. I believe that knowledge is useless unless you share it; the more you share, the more you learn.

Recent comments

Photo Stream

,