How to merge the results of two queries to different indices in Elasticsearch?

Question

I'm searching an index main-kittens for docs of type Kitty. Now, I want to run an experiment. For some of the users, I want to search experiment-kittens instead. The type is the same — Kitty, and all the fields has the same value as in main index, but while the field Bio is always empty in the main index, in experimental one it stores huge strings.

Now, the problem is that I can't store that Bio for all kittens due to memory/disk limitations. So the experiment-kittens has only most recent kittens (say, last month).

I want the search to be left intact for the most users (i.e., always use the main index). For the picked ones, I want to merge the results. The logic should be:

search userquery + date_created < 1 month ago in experiment-kittens
search userquery + date_created > 1 month ago in main-kittens

The results should be sorted by create_date, and there are too many of them to sort them in my app.

Is there a way to ask elastic to execute two different queries on two indices and merge the results?

(I'm also sure there could be more optimal solutions to the problem, please tell me if you have some).

Answer 1

You can search across multiple indices with a single Elasticsearch request by separating the index names with a comma. Then you can use the missing filter to differentiate between the two indices (one having Bio field and the other not). Then you can use the range filter to filter based on the value of date_created field. Finally you can use the sort API to sort based on the values of date_created field.

Putting all of these together, the Elasticsearch query that you need is as under:

POST main-kittens,experiment-kittens/Kitty/_search
{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "bool": {
               "should": [
                  {
                     "bool": {
                        "must": [
                           {
                              "missing": {
                                 "field": "Bio"
                              }
                           },
                           {
                              "range": {
                                 "date_created": {
                                    "to": "now-1M"
                                 }
                              }
                           }
                        ]
                     }
                  },
                  {
                     "bool": {
                        "must_not": [
                           {
                              "missing": {
                                 "field": "Bio"
                              }
                           }
                        ],
                        "must": [
                           {
                              "range": {
                                 "date_created": {
                                    "from": "now-1M"
                                 }
                              }
                           }
                        ]
                     }
                  }
               ]
            }
         }
      }
   },
   "sort": [
      {
         "date_created": {
            "order": "desc"
         }
      }
   ]
}

You can replace "match_all": {} with any custom query that you may have.