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

[adarshzphin]

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 with term query its working only for 1 or some ids. I need to filter out around millions of ids from another index. Below is my query:

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

I am using elasticsearch-7.3.0

Please suggest!

[dadoonet (David Pilato)]

You are trying to do a "join" basically which is not really supported.
You probably to do that with a batch or similar I guess.

So scroll over all the _id from index2 and for each do a GET /index/_doc/{id}. Multiget is probably easier. Or using the ids query.