Is it possible to filter records of one index from another index in elasticsearch query?

Question

Lets say I have 100 records in index1 and 10 records in index2. And I want to get like this:

select * from index1 where id not in (select id from index2)

Can we have the same above query in elasticsearch or is it even possible.

I have tried multiple index search and it is working, however I have no clue for filter part. I am using elasticsearch-7.3.0

Please suggest!

Below query I have tried:

GET index1/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "terms": {
        "id.keyword": {
            "index" : "index2",
            "id" : "_all",
            "path" : "id"
        }
    }
        }
      ]
    } 

  }
}

Answer 1

Sub query not support by ES.You can achieve the result by using two query.

Query 1: To fetch all id values from index 2

    POST index2/_search
    {
      "_source": [
        "id"
      ]
    }

Query 2: To fetch all record in index 1 with != conditions

POST index1/_search
{
  "query": {

    "bool": {

      "must_not": [
        {
          "terms": {
            "id": [
              "val1",
              "val2"
            ]
          }

        }
      ]

    }
  }
}

Answer 2

You can use TermsLookup, this will let you add values in "where clause" from another index