Filtering by Both Indices

Siren Federate (Elasticsearch Plugin)
1

Hi,

So we’re trying to perform query + aggregation + join, but we need the search to be applied both on the current index and on the foreign one. On the documnentation, it says the query is being done on the target foreign index only.
What’s the right JSON structure to have different queries for the current and foreign indices?

Just for reference, this is our query:

{
  "query" : {
    "join" : {
      "indices" : [
        "<<foreign index>>"
      ],
      "on" : [
        "<<current join field>>",
        "foreign join field"
      ],
      "request" : {
        "query" : {
          "bool" : {
            "must" : [
              {
                "term" : {
                  "<<filter field name from foreign index>>" : {
                    "value" : 31,
                    "boost" : 1.0
                  }
                }
              },
              {
                "term" : {
                  "<<filter field name from current index>>" : {
                    "value" : "Medium",
                    "boost" : 1.0
                  }
                }
              }
            ],
            "adjust_pure_negative" : true,
            "boost" : 1.0
          }
        }
      }
    }
  },
  "aggregations" : {
    "terms#agg1" : {
      "terms" : {
        "field" : "<<by aggregation field>>",
        "size" : 2147483647,
        "min_doc_count" : 1,
        "shard_min_doc_count" : 0,
        "show_term_doc_count_error" : false,
        "order" : [
          {
            "_count" : "desc"
          },
          {
            "_key" : "asc"
          }
        ]
      },
      "aggregations" : {
        "top_hits#agg2" : {
          "top_hits" : {
            "from" : 0,
            "size" : 1,
            "version" : false,
            "explain" : false
          }
        }
      }
    }
  }
}

Cheers,
Michael

2

Hi Michael,

Since the join query is a just like any other Elasticsearch query, it can then be combined in the same way you would with regular Elasticsearch queries.

Therefore, you can use a bool query to apply on the filtered index: in the example below, this is what happens:

  • there are 2 term queries that filter your “foreign” index with values “31” and “Medium”
  • this subset is joined with the filtered index on which you apply an additional term query with value “42”.
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "<<filter field name from the filtered index>>": {
              "value": 42,
              "boost": 1
            }
          }
        },
        {
          "join": {
            "indices": [
              "<<foreign index>>"
            ],
            "on": [
              "<<current join field>>",
              "foreign join field"
            ],
            "request": {
              "query": {
                "bool": {
                  "must": [
                    {
                      "term": {
                        "<<filter field name from foreign index>>": {
                          "value": 31,
                          "boost": 1
                        }
                      }
                    },
                    {
                      "term": {
                        "<<filter field name from current index>>": {
                          "value": "Medium",
                          "boost": 1
                        }
                      }
                    }
                  ],
                  "adjust_pure_negative": true,
                  "boost": 1
                }
              }
            }
          }
        }
      ],
      "adjust_pure_negative": true,
      "boost": 1
    }
  },
  "aggregations": {
    "terms#agg1": {
      "terms": {
        "field": "<<by aggregation field>>",
        "size": 2147483647,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "_count": "desc"
          },
          {
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "top_hits#agg2": {
          "top_hits": {
            "from": 0,
            "size": 1,
            "version": false,
            "explain": false
          }
        }
      }
    }
  }
}
3

Working as magic! :crown:

1 Like