Imagine data for a team with a bunch of team members, where both members and teams have associated data. Is there a search syntax that would allow me to

1. Filter members based on user criteria (e.g., years of experience)
2. Then Aggregate some member attribute to the team level (e.g., average number of goals of filtered members)
3. Then Filter on the aggregation (e.g., only teams with avg number of goals of filtered members >= 17)
4. Then Sort on any attribute of the team (e.g., number of wins descending).

In SQL terms, this would be equivalent to WHERE, GROUP BY , HAVING, ORDER BY

If it helps, see below for sample data. In specific terms, here is a sample question I'm trying to ask:

I want all teams whose average number of goals of players with at least E years of experience is > G, sorted by the team's number of wins in descending order.

Note: Because the user can filter on anything, saving pre-aggregated data in the index is not an option. I need search syntax that will enable me to do this dynamically, on the fly.

Schema

PUT sample
{
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "teamId": { "type": "keyword", "index": true, "doc_values": true },
      "teamAge": { "type": "integer", "index": true, "doc_values": true },
      "wins": { "type": "integer", "index": true, "doc_values": true },
      "losses": { "type": "integer", "index": true, "doc_values": true },
      "memberId": { "type": "keyword", "index": true, "doc_values": true },
      "exp": { "type": "float", "index": true, "doc_values": true},
      "age": { "type": "integer", "index": true, "doc_values": true},
      "height": { "type": "integer", "index": true, "doc_values": true},
      "weight": { "type": "integer", "index": true, "doc_values": true},
      "goals": { "type": "integer", "index": true, "doc_values": true},
      "code": { "type": "keyword", "index": true, "doc_values": true}
    }
  }
}

Sample Data

PUT sample/_doc/1  
{ "teamId" : "A", "teamAge":7 , "wins": 10, "losses": 5, "memberId" : "A1" , "exp": "1" , "age" : "21" , "code": "X", "goals": 12, "height": 72, "weight": 170 } 
PUT sample/_doc/2  
{ "teamId" : "A", "teamAge":7 , "wins": 10, "losses": 5, "memberId" : "A2" , "exp": "2" , "age" : "31" , "code": "Y", "goals": 15, "height": 75, "weight": 190 } 
PUT sample/_doc/3  
{ "teamId" : "A", "teamAge":7 , "wins": 10, "losses": 5, "memberId" : "A3" , "exp": "3" , "age" : "41" , "code": "Z", "goals": 20, "height": 80, "weight": 210 } 
PUT sample/_doc/4  
{ "teamId" : "B", "teamAge":6 , "wins": 12, "losses": 3, "memberId" : "B1" , "exp": "1" , "age" : "22" , "code": "Z", "goals": 20, "height": 80, "weight": 220 } 
PUT sample/_doc/5  
{ "teamId" : "B", "teamAge":6 , "wins": 12, "losses": 3, "memberId" : "B2" , "exp": "2" , "age" : "32" , "code": "X", "goals": 18, "height": 78, "weight": 200 } 
PUT sample/_doc/6  
{ "teamId" : "B", "teamAge":6 , "wins": 12, "losses": 3, "memberId" : "B3" , "exp": "3" , "age" : "42" , "code": "X", "goals": 16, "height": 76, "weight": 185 } 
PUT sample/_doc/7  
{ "teamId" : "B", "teamAge":6 , "wins": 12, "losses": 3, "memberId" : "B4" , "exp": "4" , "age" : "52" , "code": "Y", "goals": 12, "height": 72, "weight": 170 } 
PUT sample/_doc/8  
{ "teamId" : "C", "teamAge":5 , "wins": 11, "losses": 4, "memberId" : "C1" , "exp": "1" , "age" : "23" , "code": "Y", "goals": 16, "height": 76, "weight": 190 } 
PUT sample/_doc/9  
{ "teamId" : "C", "teamAge":5 , "wins": 11, "losses": 4, "memberId" : "C2" , "exp": "2" , "age" : "33" , "code": "X", "goals": 18, "height": 78, "weight": 195 } 
PUT sample/_doc/10 
{ "teamId" : "C", "teamAge":5 , "wins": 11, "losses": 4, "memberId" : "C3" , "exp": "2" , "age" : "43" , "code": "Z", "goals": 19, "height": 79, "weight": 225 } 

CodePudding user response：

The following query gets all the teams with team members with at least 1 year of experience with an average number of goals greater than 10 sorted by the team total of wins descending.

POST sample/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "exp": {
              "gt": 1
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "teams": {
      "terms": {
        "field": "teamId",
        "size": 100,
        "order": {
          "numberOfWins": "desc"
        }
      },
      "aggs": {
        "numberOfWins": {
          "max": {
            "field": "wins"
          }
        },
        "averageGoals": {
          "avg": {
            "field": "goals"
          }
        },
        "averageGoals_filter": {
          "bucket_selector": {
            "buckets_path": {
              "total": "averageGoals"
            },
            "script": "params.total > 10"
          }
        }
      }
    }
  },
  "size": 0
}