Group By Aggregation-CodePudding

I have searched for similar questions and have not found a question that allows me to group the count of an attribute with another attribute. I have a SQL Query in this format:

SELECT weight, COUNT(age)
FROM patient_table
GROUP BY weight
ORDER BY COUNT(age) DESC;

In an elastic search database I have this information:
{"height": 170, "weight": 200, "age": 18}, {"height": 180, "weight": 250, "age": 25},...
I want to translate this SQL query to a string query in elastic search. Therefore, I want the count of the age grouped by the weight and then to return the top value. I do not know how to pass a select statement to the elastic search query, but I have figured out how to use terms aggregation to group by weight. I assume that I can just grab the top value after it has been ordered and this will be the answer I need. This has been my attempt thus far:

GET /patient_table/_search
{
    "aggs": {
        "patient": {
            "terms": {"field": "weight.keyword"},
            "order": {"_count": "desc"}
                   }
            }
}

EDIT: YD9's solution works, but is there anyway to create a max sub-aggregation to obtain the max value for the previous aggregations? I ask because when I try to create a sub-aggregation after the count, I get an incorrect value of null for the max_value. This is my attempt:

{
    "size": 0,
      "aggs": {
        "weigth": {
          "terms": {
            "field": "weight.keyword",
            "size": 10,
            "order": {
              "age_count_by_weight": "desc"
            }
          },
          "aggs": {
            "age_count_by_weight": {
              "value_count": {"field": "age"}
            }
          }
        }
      },
      "aggs": {
          "max_age": {
              "max": {"field": "age"}
        }
   }
}

Any help would be appreciated. Thank you in advance.

CodePudding user response：

If you want to group by weight and order by the total number of ages for each weight, following query should work.

{
  "size": 0,
  "aggs": {
    "weight": {
      "terms": {
        "field": "weight.keyword",
        "size": 10,
        "order": {
          "age_count_by_weight": "desc"
        }
      },
      "aggs": {
        "age_count_by_weight": {
          "cardinality": {
            "field": "age"
          }
        }
      }
    }
  }
}

Edit: cardinalty aggregations counts each age once. If you want to count total number of documents, this query should work:

  "size": 0,
  "aggs": {
    "weigth": {
      "terms": {
        "field": "weight.keyword",
        "size": 10,
        "order": {
          "age_count_by_weight": "desc"
        }
      },
      "aggs": {
        "age_count_by_weight": {
          "value_count": {
            "field": "age"
          }
        }
      }
    }
  }
}

Edit 2: To get max of age, you can use max_buckets aggregation. This is the sample query

{
  "size": 0,
  "aggs": {
    "weigth": {
      "terms": {
        "field": "weight.keyword",
        "size": 10,
        "order": {
          "age_count_by_weight": "desc"
        }
      },
      "aggs": {
        "age_count_by_weight": {
          "value_count": {
            "field": "age"
          }
        }
      }
    },
    "max_age": {
      "max_bucket": {
        "buckets_path": "weigth>age_count_by_weight"
      }
    }
  }
}