Passing reduced ES query results to SQL

Question

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 aggregation query. Here's an example:

Let's say we search "Terminator" in a financials database that has 10B records. It has the following matches:

• "Terminator" (1M results)
• "Terminator 2" (10M results)
• "XJ4-227" (1 result ==> Here "Terminator" is in the synopsis of the title)

Instead of passing back the 10+M ids, we'd pass back the following 'reduced query' --

...WHERE name in ('Terminator', 'Terminator 2', 'XJ4-227')

How could we write such an algorithm to reduce the ES result set to a smallest possible filter query that we could send back to SQL? Does ES have any sort of match-metadata that would help us in this?

Answer 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.