Create match query on 2 tables with elasticsearch 2.3

Question

As I can make a query between 2 tables, I have the following tables

Company:
- id
- name
- domain
- timestamp

Target:
- id
- name
- domain
- company
- website
- company_id
- timestamp

User:
- id
- firstName
- lastName
- email
- company_id
- timestamp

I need to make a match between domain of target and the user's mail belonging to the company.

I'm working on ElasticSearch 2.3 with the elasticsearch-py library with python 2.7

These answers might help: http://stackoverflow.com/questions/35153578/map-a-book-in-elasticsearch-with-many-levels-nested-vs-parent-child-relationshi/35153715#35153715 + http://stackoverflow.com/questions/40008948/elasticsearch-query-with-intermediate-results/40013303#40013303 + http://stackoverflow.com/questions/34477816/how-to-use-elasticsearch-to-get-join-functionality-as-in-sql/34477920#34477920 + http://stackoverflow.com/questions/33288503/elasticsearch-map-two-sql-tables-with-a-foreign-key/33294753#33294753 — Val, Nov 16 '16 at 04:44

score 0 · Answer 1 · answered Nov 16 '16 at 08:20

The types in Elasticsearch cannot be join with each other like SQL. But you can use parent-child relationship to let types relate to each other.

In this case maybe you can set the [Target] and [User] table to the child of [Company] and query like:

"query": {
    "bool": {
        "must": [
           {
               "has_child": {
                   "query": {
                       "match": {
                          "domain": "test domain"
                       }
                   },
                   "child_type" : "Target"
               }
           },
           {
               "has_child": {
                   "query": {
                       "match": {
                          "email": "test email"
                       }
                   },
                   "child_type" : "User"
               }
           }
        ],
        "filter" : {
            "term": {
               "_type": "Company"
            }
        }
    }
}

Create match query on 2 tables with elasticsearch 2.3

1 Answers1