Passing Reduced Es Query Results To Sql

This is a follow-up question to How to pass ElasticSearch query to hadoop. Basically, I want to do a full-text-search in ElasticSearch and then pass the result set to SQL to run an

Solution 1:

If you know that which "not analyzed" (keyword at 5.x) field would be suitable for your use case you could get their distinct values and number of matches by terms aggregation. sum_other_doc_count even tells you if your search resulted in too many distinct values, as only top N are returned.

Naturally you could run terms aggregation on multiple fields and use the one in SQL which had fewest distinct values. And actually it could be more efficient to first run cardinality aggregation to know to which field you should run terms aggregation.

If your search is a pure filter then its result should be cached but please benchmark both solutions as your ES cluster has quite a lot of data.