Query domain-specific language (DSL)

Join query

The join filter enables the filtering of one set of documents, considered as the parent set, with another, considered as the child set, based on shared field values. It accepts the following parameters:

type

The type of join algorithm to use. Valid values are BROADCAST_JOIN, HASH_JOIN, INDEX_JOIN, or ROUTING_JOIN. If this parameter is not specified, the query planner will automatically select the optimal one. For more information, see Configuring joins by type.

indices

The index names for the child set. Multiple indices can be specified using the Elasticsearch syntax. Defaults to all indices.

on

An array of two elements that is used as an equality join condition between two field paths - one for the parent and the other for the child set, respectively.

request

The search request that is used to compute the set of documents of the child set before performing the join.

Example

In this example, we will join all the documents from parent_index with the documents of child_index using the HASH_JOIN algorithm. The query first filters documents from child_index and of type type with the query { "terms" : { "tag" : [ "aaa" ] } }. It then retrieves the ids of the documents from the field id specified by the parameter on. The list of ids is then used as filter and applied on the field foreign_key of the documents from parent_index.

curl -H 'Content-Type: application/json' -XGET 'http://localhost:9200/siren/parent_index/_search' -d '
{
  "query" : {
    "join" : {
      "type": "HASH_JOIN",
      "indices" : ["child_index"],
      "on" : ["foreign_key", "id"], (1)
      "request" : { (2)
        "query" : {
          "terms" : {
            "tag" : [ "aaa" ]
          }
        }
      }
    }
  }
}
'
1 The equality join condition: the value of the foreign_key field must be equal to the value of the id field.
2 The search request that will be used to filter out the child set (i.e. child_index)

Extended join condition syntax

The traditional join syntax is a powerful tool for combining relational data from multiple tables, but sometimes the join condition may require more complex logic than just comparing keys for equality. For this purpose, the extended join condition syntax has been introduced, which offers a more flexible and customizable approach to define join conditions. By using the extended syntax, we can create joins with complex conditions, including those that combine multiple conditions and various join condition operators. To establish an extended join condition, the following syntax is used:

"on": {
  "<boolean expression operator>": {
    "<binary operator>": {
      "fields": ["<left_field>","<right_field>"]
    }
on

An object to specify the join condition configuration.

<boolean expression operator>

The boolean expression operator specifies how the binary operator will be interpreted. Currently, one can only use must.

<binary operator>

The operator used to compare the left_field and the right_field from the parent and the child set.

fields

An array of two elements that specifies the field paths for the join keys in the parent and the child set, respectively.

Following is the list of binary operators, and their description.

Operator Example Syntax sugar Description

eq

 
"on": {
  "must":{
    "eq":{
      "fields": [
        "left_field",
        "right_field"
      ]
    }
  }
}
 
"on" : [
  "left_field",
  "right_field"
]

left_field is equal to right_field

lt

 
"on": {
  "must":{
    "lt":{
      "fields":[
        "left_field",
        "right_field"
      ]
    }
  }
}

left_field is less than right_field

lte

 
"on": {
  "must":{
    "lte":{
      "fields":[
        "left_field",
        "right_field"
      ]
    }
  }
}

left_field is less than or equal to right_field

gt

 
"on": {
  "must":{
    "gt":{
      "fields":[
        "left_field",
        "right_field"
      ]
    }
  }
}

left_field is greater than right_field

gte

 
"on": {
  "must":{
    "gte":{
      "fields":[
        "left_field",
        "right_field"
      ]
    }
  }
}

left_field is greather than or equal to right_field
Check the requirements for executing a join section to see the accepted data types for each operator.

Multi-conditional join

A multi-conditional join can be implemented by expressing the join condition as a composition of binary relations. The must field, which was previously described, can be used as an array of condition statements instead of an object, allowing for more than one condition in a join. It is important to note that the must field represents an array of conditions that must be met in a document for the join to occur. Essentially, the join is a conjunction of all the conditions provided.

must

An array of join conditions. The join is the conjunction of all the conditions provided, i.e., all conditions must be met in a document for the join to occur.

Example

curl -H 'Content-Type: application/json' -XGET 'http://localhost:9200/siren/parent_index/_search' -d '
{
  "query" : {
    "join" : {
      "indices" : ["child_index"],
      "on" :
        {
          "must": [ (1)
            {"eq": {"fields": ["foreign_key", "id"] }}, (2)
            {"gt": {"fields": ["foreign_key_2", "field2"] }} (3)
          ],
        }
        "request" : {
          "query" : {
            "match_all" : {}
          }
        }
    }
  }
}
'
1 The join condition is a conjunction of 2 separate conditions.
2 Match documents where the foreign_key field of parent_index is equal to the id field of child_index.
3 In addition, this matches documents where the foreign_key_2 field of parent_index is greater than field2 field of child_index.
While you have the flexibility to add numerous conditions, it is important to keep in mind that each additional condition can potentially affect performance.
If all conditions are using equality operators, consider using runtime fields instead and create a composite key.

Project

When joining a child set with a parent set, the fields from the child set may be projected to the parent set. The projected fields and associated values are mapped to the matching documents of the parent set.

A projection is defined in the request body search of the join clause using the parameter project. The project parameter accepts an array of field’s objects, each one defining a field to project.

The projected fields from a child set are accessible in the scope of the parent’s request. One can refer to a projected field in a project context or in a script context such as in a script field, a script-based sort, and so on.

For information about accepted projected fields in a join, see Requirements for a join .

Field

A standard field object specifies the projection of a field from a set. It is composed of the following parameters:

name

The name of a field from a child set to project.

alias

An alias name to give to the projected field. It is not possible to have multiple fields with identical names in the same set scope as this leads to ambiguity. It is therefore important to carefully select alias names to avoid such ambiguity.

Following is an example of a projection:

{
"join" : {
  "indices" : "people",
  "on" : ["id", "employed_by"],
  "request" : {
    "project" : [ (1)
      {
        "field" : {
          "name" : "age", (2)
          "alias" : "employee_age" (3)
        }
      }
    ],
    "query" : {"match_all" : {} }
  }
}
1 An array of fields to project
2 The name of the field to project
3 An alias for the field name