Allow passing a query in Terms Query's "Terms lookup mechanism"

[gcampbell-epiq]

Describe the feature: Extend the Terms Query to allow another query to be used as the "Terms lookup mechanism."

Currently, a specific document can be referenced from the terms query as a means of passing a lot of values to the terms query. Documented here: Terms Query - Terms lookup mechanism. I'm proposing making the lookup's "id" parameter optional, and adding a "query" parameter which supports the full query DSL.

This would give ElasticSearch a functional equivalent to this common SQL statement.

SELECT * 
FROM TableA a
WHERE a.Field1 IN (SELECT DISTINCT b.Field2
                   FROM TableB b
                   WHERE b.Field3 LIKE '%blah%')
-- TableA & TableB could of course be the same table
-- Field1, Field2, and Field3 could of course be the same field

Given my proposed change to the terms query, the equivalent in ES Query DSL to this SQL would be:

POST /TableA/_search
{
    "query": {
        "terms": {
            "Field1": {
                "path": "Field2",
                "index": "TableB",
                "query": { //query parameter here, instead of id parameter
                    "query_string": { "query": "*blah*", "default_field": "Field3" }
                }
            }
        }
    }
}

[gcampbell-epiq]

I know this could be accomplished with multiple calls, but I'm afraid of the overhead in that.

[wenhoujx]

#3278

[clintongormley]

As @wenhoujx pointed out, we decided not to add this functionality in #3278 because it is not horizontally scalable.