Cascaded querying in ElasticSearch

[Rory]

I am not a hundred percent sure what the title for this question should be. But here goes,

I have some data which looks like this:

There are 3 columns; ID, Name, FavoriteSoccerPlayer

I can write a query to return all documents where Name = "A" or "C". The query looks like:

GET /myindex/mytype/_search
{
  "size": 0,
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "Name": "A"
          }
        },
        {
          "match": {
            "Name": "C"
          }
        }
      ]
    }
  }
}

This query returns documents with value of ID column in (1,3,4,6,8,9). The unique list of FavoriteSoccerPlayer returned by this query is ('Ronaldo','Henry','Buffon').

What I want to do next is write a query which returns all documents where the FavoriteSoccerPlayer attribute is ('Ronaldo','Henry','Buffon'). The result of this query should be documents with ID 1,3,4,5,6,8,9,10.

I come from a SQL background, and this is similar to writing a cascaded query. Any help is highly appreciated.

Thanks.

[Rory]

Bump.

[mmichaels01]

Have you tried this?

GET /myindex/mytype/_search
{
  "size": 10,
  "query": {
    "bool": {
      "should": [
        {
          "match": {
            "FavoriteSoccerPlayer": "Ronaldo"
          }
        },
        {
          "match": {
            "FavoriteSoccerPlayer": "Henry"
          },
        {
          "match": {
            "FavoriteSoccerPlayer": "Buffon"
          }
        }
      ]
    }
  }
}

[Rory]

I want the final query to run dynamically based on the results of the first query. This query is a static version just for this example.

Thanks.

[mmichaels01]

I see what you're saying. A scripted field may be able to get you the desired result here.

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-script-fields.html

If that doesn't work, you may need something external performing the second query based on your first response.

I'm not aware of any out of the box functionality that would work exactly as you describe, however, it's nothing I've ever read about or attempted to do myself.