Joining your data
Examples in this section are based on three indices (company
, person
and article
)
that can be created and loaded using the following request:
curl -H 'Content-Type: application/json' -XPUT 'http://localhost:9200/_bulk?pretty' -d '
{ "index": { "_index": "company", "_id": "1" } }
{ "id": 1, "name": "Acme", "ceo": "peo1", "ceo_full_name": "Alice Wonder" }
{ "index": { "_index": "company", "_id": "2" } }
{ "id": 2, "name": "Bueno"}
{ "index": { "_index": "company", "_id": "3" } }
{ "id": 3, "name": "Ark" }
{ "index": { "_index": "person", "_id": "peo1" } }
{ "id": 1, "first_name": "Alice", "last_name": "Wonder", "employed_by": "1", "age": 50 }
{ "index": { "_index": "person", "_id": "peo2" } }
{ "id": 2, "first_name": "Bob", "last_name": "Dylan", "employed_by": "2", "age": 20 }
{ "index": { "_index": "person", "_id": "peo3" } }
{ "id": 3, "first_name": "Carol", "last_name": "Aird", "employed_by": "1", "age": 25 }
{ "index": { "_index": "article", "_id": "art1" } }
{ "id": 1, "title": "ACME Raises $1 Billion in New Funding", "mentions_company": 1 }
{ "index": { "_index": "article", "_id": "art2" } }
{ "id": 2, "title": "Best Companies of 2014", "mentions_company": [1, 2] }
{ "index": { "_index": "article", "_id": "art3" } }
{ "id": 3, "title": "What Type of Pizza Are You ?", "mentions_company": 3 }
'
These documents are indexed using the dynamic field mapping feature provided by Elasticsearch.
Join based on the metadata field _id
The metadata field _id
is supported as a join key field in semi-join queries.
Example
Having indexed the company and person indices and indexed documents, you can consider joining the two indices to fetch a list of companies whose employees are in the person
index by using the following request:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"], (1)
"request" : {
"query" : {
"match_all" : {}
}
}
}
}
}'
1 | The metadata field _id of the index company is used as the left join key field |
The response should contain two hits, as follows:
{
"hits": [
{
"_index": "company",
"_id": "1",
"_score": 1,
"_source": {
"id": 1,
"name": "Acme",
"ceo": "peo1",
"ceo_full_name": "Alice Wonder"
}
},
{
"_index": "company",
"_id": "2",
"_score": 1,
"_source": {
"id": 2,
"name": "Bueno"
}
}
]
}
Suppose that the two indices are joined in order to retrieve a list of companies using the following request:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["ceo.keyword", "_id"], (1)
"request" : {
"query" : {
"match_all" : {}
}
}
}
}
}'
1 | The metadata field _id of the index person is used as the right join key field |
The response should contain one hit, as follows:
{
"hits" : {
"total" : 1,
"max_score" : 1.0,
"hits" : [
{
"_index": "company",
"_id": "1",
"_score": 1,
"_source": {
"id": 1,
"name": "Acme",
"ceo": "peo1",
"ceo_full_name": "Alice Wonder"
}
}
]
}
}
Join based on a runtime field
Runtime fields can also be used as part of a join. They can be as part of the parent index or the child index, as shown in the following example:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["rt_field_company", "rt_field_person"], (1)
"request" : {
"runtime_mappings": {
"rt_field_person": {
"type": "long",
"script": {
"source": "<put your script here>" (2)
}
}
},
"query" : {
"match_all" : {}
}
}
}
},
"runtime_mappings": {
"rt_field_company": {
"type": "long",
"script": {
"source": "<put your script here>" (3)
}
}
}
}'
1 | Join on two runtime fields defined in the request |
2 | Runtime field for person index |
3 | Runtime field for company index |
In this example, the join operation is performed on two runtime fields: rt_field_company
and rt_field_person
. The runtime mappings are defined within the request for both the parent (company) and child (person) indices. Replace <put your script here>
with the appropriate Painless script to compute the desired values for each runtime field.
Join based on a composite key
Runtime fields can be used to create composite keys, that can be used to join on multiple conditions. For example, if you have first_name
and last_name
fields, you often want to join on both fields at the same time. You can use runtime fields to concatenate multiple fields, and use this composite key as a join key.
Another use case example is if you have fields for IP address and network port, you can create a composite key <ip_address>:<port>
.
Like in the previous section, they can be defined for the parent index, the child index, or both. An example involving the company and person indices could look like this:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '
{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["ceo_full_name.keyword", "rt_full_name"], (1)
"request" : {
"runtime_mappings": {
"rt_full_name": { (2)
"type": "keyword",
"script": {
"source": "emit(doc[\"first_name.keyword\"].value + \" \" + doc[\"last_name.keyword\"].value)"
}
}
},
"query" : {
"match_all" : {}
}
}
}
}
}'
1 | Join between an indexed field and a runtime field |
2 | Runtime field for the person index, containing the concatenation of multiple fields |
In this example, a join operation is performed between the ceo_full_name
field of the company
index and a runtime field rt_full_name
. The runtime field is a composite key: a concatenation of the first_name
and last_name
of employees in the person
index.
This allows to join indices even when they don’t have directly corresponding keys.
Retrieving a projected field
You can use the fields
parameter to retrieve the values of a projected field for each hit.
For example, the two indices can be joined to retrieve a list of companies along with the ages of all their employees using the following request:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '
{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"],
"request" : {
"project" : [
{ "field" : { "name" : "age", "alias" : "employees_age" } } (1)
],
"query" : {
"match_all" : {}
}
}
}
},
"fields" : ["employees_age"] (2)
}
'
1 | Projects the field age from index person as employees_age . |
2 | Returns the projected field employees_age using the fields parameter. |
The response contains two hits, one for each company, with the field employees_age
as follows:
{
"hits": {
"total": {
"value": 2,
"relation": "eq"
},
"max_score": 1,
"hits": [
{
"_index": "company",
"_id": "1",
"_score": 1,
"_source": {
"id": 1,
"name": "Acme",
"ceo": "peo1",
"ceo_fullname": "Alice Wonder"
},
"fields": {
"employees_age": [
25,
50
]
}
},
{
"_index": "company",
"_id": "2",
"_score": 1,
"_source": {
"id": 2,
"name": "Bueno"
},
"fields": {
"employees_age": [
20
]
}
}
]
}
}
Retrieving a runtime field
The runtime field mapping defined in the search request allows creating a field that exists only as part of the query.
This new field can be projected like any other field by using the fields
parameter.
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '
{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"],
"request" : {
"runtime_mappings": {
"rt_field": {
"type": "long",
"script": {
"source": "<put your script here>" (1)
}
}
},
"project" : [
{ "field" : { "name" : "rt_field" } } (2)
],
"query" : {
"match_all" : {}
}
}
}
},
"fields" : ["rt_field"] (3)
}'
1 | The runtime field defined in the request |
2 | To project the new field |
3 | To fetch the new field into the response if needed |
In this example, a runtime field named rt_field
is created with a Painless script.
Replace <put your script here>
with the appropriate script to compute the desired value for the runtime field.
The new runtime field is projected and retrieved into the response using the fields
parameter.
Retrieving a federate_object runtime field
This federate_object
runtime field can be retrieved like any other runtime field by using the
fields
parameter like described in the
query DSL
section.
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '
{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"],
"request" : {
"runtime_mappings": {
"firstname": {
"type" : "keyword",
"script" : {
"source":"emit(doc[\"first_name.keyword\"].value)",
"lang":"painless"
}
},
"employee": { (1)
"type": "federate_object",
"fields": ["firstname", "last_name.keyword", "age"]
}
},
"project" : [
{ "field" : { "name" : "employee" } } (2)
],
"query" : {
"match_all" : {}
}
}
}
},
"fields" : ["employee"] (3)
}'
1 | The federate_object runtime field defined in the request |
2 | To project the new runtime field |
3 | To fetch the new field into the response if needed |
The response should contain two hits, as follows:
{
"hits": [
{
"_index": "company",
"_id": "1",
"_score": 1,
"fields": {
"employee": [
{
"first_name": "Alice",
"last_name.keyword": "Wonder",
"age": 50
},
{
"first_name": "Carol",
"last_name.keyword": "Aird",
"age": 25
}
]
}
},
{
"_index": "company",
"_id": "2",
"_score": 1,
"fields": {
"employee": [
{
"first_name": "Bob",
"last_name.keyword": "Dylan",
"age": 20
}
]
}
}
]
}
Projecting and retrieving nested federate_object fields
Runtime fields of type federate_object
are designed to support nesting.
As an example, if you wanted to retrieve the following chain of nested `federate_object`s:
-
article
→company
→person
It can be done in the same way as using ordinary runtime fields as follows:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/article/_search?pretty' -d '
{
"query" : {
"join" : {
"indices" : ["company"],
"on" : ["mentions_company", "id"],
"request" : {
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"],
"request" : {
"query" : {
"match_all" : {}
},
"runtime_mappings": {
"employees": { (1)
"type" : "federate_object",
"fields" : ["first_name.keyword", "last_name.keyword"]
}
},
"project" : [
{ "field" : { "name" : "employees" } } (2)
]
}
}
},
"runtime_mappings": {
"company" : { (1)
"type" : "federate_object",
"fields" : ["name.keyword", "employees"]
}
},
"project" : [
{ "field" : { "name" : "company", "alias": "companies_mentioned" } } (2)
]
}
}
},
"fields" : ["companies_mentioned"] (3)
}
'
1 | Defines a federate_object runtime field in the search request |
2 | Projects the federate_object runtime field from the child index |
3 | Returns in the response the federate_object runtime field projected from the child index |
The response should contain the following hit:
{
"hits": {
"total": {
"value": 2,
"relation": "eq"
},
"hits": [
{
"_index": "article",
"_id": "art1",
"_source": {
"id": 1,
"title": "ACME Raises $1 Billion in New Funding",
"mentions_company": 1
},
"fields": {
"companies_mentioned": [
{
"name.keyword": "Acme",
"employees": [
{
"last_name.keyword": "Wonder",
"first_name.keyword": "Alice"
},
{
"last_name.keyword": "Aird",
"first_name.keyword": "Carol"
}
]
}
]
}
},
{
"_index": "article",
"_id": "art2",
"_source": {
"id": 2,
"title": "Best Companies of 2014",
"mentions_company": [1, 2]
},
"fields": {
"companies_mentioned": [
{
"name.keyword": "Acme",
"employees": [
{
"first_name.keyword": "Alice",
"last_name.keyword": "Wonder"
},
{
"first_name.keyword": "Carol",
"last_name.keyword": "Aird"
}
]
},
{
"name.keyword": "Bueno",
"employees": {
"first_name.keyword": "Bob",
"last_name.keyword": "Dylan"
}
}
]
}
}
]
}
}
Projecting and retrieving fields of a nested data type
Runtime fields can be used to project fields of a nested
data type. For instance, consider the following indices
curl -H 'Content-Type: application/json' -XPUT 'http://localhost:9200/teachers' -d '
{
"mappings": {
"properties": {
"id": { "type": "keyword" },
"classes": {"type": "keyword"}
}
}
}
'
curl -H 'Content-Type: application/json' -XPUT 'http://localhost:9200/classes' -d '
{
"mappings": {
"properties": {
"id": { "type": "keyword" },
"students": {
"type": "nested",
"properties": {
"name": {
"type": "keyword"
},
"surname": {
"type": "keyword"
}
}
}
}
}
}
'
As an example, we want to retrieve the full names of the teachers' students. These can be projected using runtime fields, and retrieved using the fields
parameter as follows
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/teachers/_search?pretty' -d '{
"query": {
"join": {
"indices": [
"classes"
],
"on": [
"classes",
"id"
],
"request": {
"runtime_mappings": {
"fullname": {
"type": "keyword",
"script": {
"source": "for (s in params._source.students) { emit(s.name + ' ' + s.surname) }" (1)
}
}
},
"project": {
"field": {
"name": "fullname" (2)
}
},
"query": {
"match_all": {}
}
}
}
},
"fields": ["fullname"] (3)
}'
1 | The runtime field defined in the request |
2 | To project the new field |
3 | To fetch the new field into the response if needed |
Filter using a projected field
Runtime fields can also be used to filter on projected data.
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '
{
"runtime_mappings": {
"employee_count": {
"type": "long",
"script": "emit(doc.employees.length)" (1)
}
},
"query": {
"bool": {
"must": [
{
"join": {
"indices": ["person"],
"on": ["_id", "employed_by.keyword"],
"request": {
"project": [
{
"field": {"name": "id", "alias": "employees"} (2)
}
],
"query": {
"match_all": {}
}
}
}
}
],
"filter": [
{
"range" : {
"employee_count" : { "gt" : 1 } (3)
}
}
]
}
}
}'
1 | The runtime field used to collect the projected fields |
2 | To project the id field for all matching documents in the person index |
3 | To filter the documents in the company index, using the value from the runtime field |
In this example (using the company and person indices), the runtime field employee_count
collects all the projected values from the person
index, and returns the number of values.
This runtime field is then used in the filter
clause to return only the companies that have more than 1 employee.
Sorting based on a projected field
A script-based sorting method can be used to sort the hits based on the values of a projected field, for example (using the company and person indices):
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"],
"request" : {
"project" : [
{ "field" : { "name" : "age", "alias" : "employee_age" } }
],
"query" : {
"match_all" : {}
}
}
}
},
"runtime_mappings": {
"employee_ages": {
"type": "long",
"script": "int sum = 0; for (value in doc.employee_age) { sum += value } emit(sum);"
}
},
"sort": [
{
"employee_ages": "desc"
}
]
}'
The response should contain two hits, one for each company, sorted by the sum of their employees age as follows:
{
"hits": [
{
"_index": "company",
"_id": "1",
"_score": null,
"_source": {
"id": 1,
"name": "Acme",
"ceo": "peo1",
"ceo_fullname": "Alice Wonder"
},
"sort": [
75
]
},
{
"_index": "company",
"_id": "2",
"_score": null,
"_source": {
"id": 2,
"name": "Bueno"
},
"sort": [
20
]
}
]
}
In this example, the join operation is performed between the company
and person
indices, and the projected field employee_age
is created. The script-based sorting method is used to sort the hits based on the sum of employees' ages in the employee_ages
runtime field. The search results are sorted in descending order, with the company having the highest sum of employees' ages appearing first in the search results.
Spatial join using range fields
Federate enables spatial joins through the application of one or more spatial conditions on range fields. For example, documents with a date
field in the parent index can be joined with the child index when the date
is within the retention_dates
field of the child index; where retention_dates
is of type date_range
.
This join can be expressed as follows:
curl -H 'Content-Type: application/json' -XGET 'http://localhost:9200/siren/parent_index/_search' -d '
{
"query" : {
"join" : {
"indices" : ["child_index"],
"on" :
{
"must": [ (1)
{"within": {"fields": ["date", "retention_dates"] }} (2)
],
}
"request" : {
"query" : {
"match_all" : {}
}
}
}
}
}
'
1 | The join using range field data types. |
2 | Match documents where the date is within the ranges defined by retention_dates . |
In this example, a spatial join is performed between the parent_index
and child_index
based on the date
and retention_dates
fields. The join condition requires the parent index’s date
field to be within the range defined by the retention_dates
field.
If the range data type field is located on the parent_index
, use the contains
operator instead.
curl -H 'Content-Type: application/json' -XGET 'http://localhost:9200/siren/parent_index/_search' -d '
{
"query" : {
"join" : {
"indices" : ["child_index"],
"on" :
{
"must": [ (1)
{"contains": {"fields": ["retention_dates", "date"] }} (2)
],
}
"request" : {
"query" : {
"match_all" : {}
}
}
}
}
}
'
1 | The join using range field data types. |
2 | Match documents where the range in retention_dates contains the date value. |
For information about accepted data types in a spatial join, see Requirements for a join and the known limitations section for spatial joins. |
Spatial join using simple fields
In addition to range fields, Federate enables spatial joins through the application of one or more spatial conditions on fields with basic data types such as numeric or date. For example, documents with a date
field in the parent index can be joined with the child index where the date is within a start_date
field and a end_date
field of the child index. This translates to the Boolean expression date > start_date && date < end_date
.
This spatial join can be expressed as follows:
curl -H 'Content-Type: application/json' -XGET 'http://localhost:9200/siren/parent_index/_search' -d '
{
"query" : {
"join" : {
"indices" : ["child_index"],
"on" :
{
"must": [ (1)
{"gt": {"fields": ["date", "start_date"] }}, (2)
{"lt": {"fields": ["date", "end_date"] }} (3)
]
},
"request" : {
"query" : {
"match_all" : {}
}
}
}
}
}
'
1 | The join is the conjunction of 2 conditions. |
2 | Match documents where date is greater than start_date . |
3 | In addition, this matches documents where date is less than end_date . |
In this example, a spatial join is performed between the parent_index
and child_index
based on the date
, start_date
, and end_date
fields. The join condition requires the parent index’s date
field to be greater than the child index’s start_date
field and less than the child index’s end_date
field.
Instead of combining GT(E) and LT(E) conditions to join on ranges, you can use range data types and their specialized operators: |
Handling missing fields
Spatial join with range fields
natively support open-ended ranges. However, in scenarios where using range fields is not feasible, you can use the allow_missing_bound
parameter to interpret a missing value for a field (if the field is not set, or set to null
) as an open bound for the range.
The join operator in the previous example can be altered to also match documents missing a value for the end_date
field:
curl -H 'Content-Type: application/json' -XGET 'http://localhost:9200/siren/parent_index/_search' -d '
{
"query" : {
"join" : {
"indices" : ["child_index"],
"on" :
{
"must": [
{
"gt": {
"fields": ["date", "start_date"] (1)
}
},
{
"lt": {
"fields": ["date", "end_date"], (2)
"allow_missing_bound": "right" (3)
}
}
]
},
"request" : {
"query" : {
"match_all" : {}
}
}
}
}
}
'
1 | Match documents where date is greater than start_date . |
2 | Also match documents where date is less than end_date . |
3 | Also match documents where the right field (end_date ) is missing a value (null or missing). |
Compatible operators and accepted syntax are detailed in the
definition of allow_missing_bounds
.
Note that when performing spatial join operations on simple fields using the |
Scoring Capabilities
By default, the join
filter returns a constant score. Therefore, the scores of the matching documents from the child set
do not affect the scores of the matching documents from the parent set. However, you can
project the document’s score from the child set
and customize the scoring of the documents from the parent set with a
script score function.
Document Score
The score of a matching document from a set may be projected using a standard field object using
the special field name _score
.
{
"field" : {
"name" : "_score",
"alias" : "employee_score"
}
}
Scoring based on a projected field
A script-based scoring method can be used to customize the scoring based on the values of a projected field. For example, you can project the score of the matching documents from the child set and aggregate them into the parent document as follows:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '{
"query": {
"function_score": {
"query": {
"join": {
"indices": [ "person" ],
"on": [ "_id", "employed_by.keyword" ],
"request": {
"project" : [
{ "field" : { "name" : "_score", "alias" : "child_score" } }
],
"query": {
"match_all": {}
}
}
}
},
"functions": [
{
"script_score": {
"script": {
"lang": "painless",
"source": "float sum = 0; for (value in doc.child_score) { sum += value } return sum;"
}
}
}
],
"score_mode": "multiply",
"boost_mode": "replace"
}
}
}'
The response should contain two hits, one for each company, sorted by the sum of their child scores as follows:
{
"hits": [
{
"_index": "company",
"_id": "1",
"_score": 2,
"_source": {
"id": 1,
"name": "Acme",
"ceo": "peo1",
"ceo_fullname": "Alice Wonder"
}
},
{
"_index": "company",
"_id": "2",
"_score": 1,
"_source": {
"id": 2,
"name": "Bueno"
}
}
]
}
Aggregating based on a projected field
A projected field can be used as part of the aggregation. For example, when using the company and person indices, you can project the values of
the field age
of the matching documents from the person
index and aggregate the documents from the company
index by using these values as follows:
curl -H 'Content-Type: application/json' 'http://localhost:9200/siren/company/_search?pretty' -d '
{
"query" : {
"join" : {
"indices" : ["person"],
"on" : ["_id", "employed_by.keyword"],
"request" : {
"project" : [
{ "field" : { "name" : "age", "alias" : "employee_ages" } }
],
"query" : {
"match_all" : {}
}
}
}
},
"aggs": {
"by_company": {
"terms": {
"field": "name.keyword"
},
"aggs": {
"average_employee_age": {
"avg": {
"field": "employee_ages"
}
}
}
}
}
}'
The response contains an aggregation result with a bucket for each company, and a sub-aggregation average_employee_age
corresponding to the average age of employees in this company.
{
"aggregations": {
"by_company": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": "Acme",
"doc_count": 1,
"average_employee_age": {
"value": 37.5
}
},
{
"key": "Bueno",
"doc_count": 1,
"average_employee_age": {
"value": 20
}
}
]
}
}
}
In this example, the query joins the company
index with the person
index using the id
and employed_by
fields. The query projects the age
field from the person
index using an alias employee_age
. The aggregation is performed using a runtime field employee_ages
, which iterates through the projected employee_age
field. The documents are first aggregated by company name, then a sub-aggregation average_employee_age
computes the average age of employees in each bucket.