[Feature Request] Enhancing Terms lookup query to take a query clause rather than docId

[navneet1v]

Is your feature request related to a problem? Please describe

Terms Lookup query is a very powerful query where it allows user to use strings stored in a document in a separate index to be passed as list of terms. But this feature is still limited where in terms lookup query you can just lookup terms from a document and you cannot pass a query which can result in terms.

Example with current capability of Terms lookup query

PUT students
{
  "mappings": {
    "properties": {
      "student_id": { "type": "keyword" }
    }
  }
}

PUT students/_doc/1
{
  "name": "Jane Doe",
  "student_id" : "111"
}

PUT students/_doc/2
{
  "name": "Mary Major",
  "student_id" : "222"
}

PUT classes/_doc/101
{
  "name": "CS101",
  "enrolled" : ["111" , "222"]
}

GET students/_search
{
  "query": {
    "terms": {
      "student_id": {
        "index": "classes",
        "id": "101",
        "path": "enrolled"
      }
    }
  }
}

Describe the solution you'd like

The solution I am proposing here is to enhance the terms lookup query to take another query clause to get the terms to be used in final query.

curl -XGET "http://localhost:9200/students/_search" -H 'Content-Type: application/json' -d'
{
  "query": {
    "terms": {
      "student_id": {
        "index": "classes",
        "path": "enrolled"
        "query": {
        	// standard OpenSearch query
        }
      }
    }
  }
}
'

Related component

Search:Query Capabilities

Describe alternatives you've considered

NA

Additional context

If we can enhance the Terms lookup query it can be used at various places one of the example can be, if a user has text documents + metadata. User wants create different vector indices for the texts but doesn't want to duplicate the metadata on different indices. User can then index the metadata separately, create multiple vector indices for different models and then use this terms lookup query to pass filters to vector search queries to refine the results.

[sandeshkr419]

[Search Triage] Thanks, this is a great idea.
cc - @bowenlan-amzn you might be interested to take a look as well here.