Collapse results based on the maximum or mininum value

Question

I have data in this format.

[
    {
        lead_id: "lead_1",
        value: 34
    },
    {
        lead_id: "lead_1",
        value: 22
    },
    {
        lead_id: "lead_1",
        value: 67
    },
    {
        lead_id: "lead_2",
        value: 12
    },
    {
        lead_id: "lead_2",
        value: 9
    },
    {
        lead_id: "lead_3",
        value: 22
    },
    {
        lead_id: "lead_3",
        value: 5
    }
]

And I want to sort the leads based on the value in desc or asc order. Also, I need to collapse the result. I used a query like this for the sort.

{
    "collapse": {
        "field": "lead_id"
    },
    "sort": {
        "value": { "order": "asc" }
    }
}

But I want to sort base on the maximum value. i.e if I sort in asc I want the results to be:

[
    {
        lead_id: "lead_2",
        value: 12
    },
    {
        lead_id: "lead_3",
        value: 22
    },
    {
        lead_id: "lead_1",
        value: 67
    }
]

and for descending

[
    {
        lead_id: "lead_1",
        value: 67
    },
    {
        lead_id: "lead_3",
        value: 22
    },
    {
        lead_id: "lead_2",
        value: 12
    }
]

Answer 1

collapse_sort as a feature is not available yet. There is an open PR so it might be available in future.

Work around is to use inner_hits

{
  "collapse": {
    "field": "lead_id.keyword",
    "inner_hits": {
      "name": "most_recent",                  
      "size": 1,                              
      "sort": [ { "value": "desc" } ]    
    }
  },
  "sort": {
    "value": {
      "order": "asc"
    }
  }
}

Result

"hits" : [
      {
        "_index" : "index11",
        "_type" : "_doc",
        "_id" : "xAugp4MBfEI_j4sNVw3V",
        "_score" : null,
        "_source" : {
          "lead_id" : "lead_3",
          "value" : 5
        },
        "fields" : {
          "lead_id.keyword" : [
            "lead_3"
          ]
        },
        "sort" : [
          5
        ],
        "inner_hits" : {  --> group sorted values
          "most_recent" : {
            "hits" : {
              "total" : {
                "value" : 2,
                "relation" : "eq"
              },
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "index11",
                  "_type" : "_doc",
                  "_id" : "wwugp4MBfEI_j4sNTw1c",
                  "_score" : null,
                  "_source" : {
                    "lead_id" : "lead_3",
                    "value" : 22
                  },
                  "sort" : [
                    22
                  ]
                }
              ]
            }
          }
        }
      },
      {
        "_index" : "index11",
        "_type" : "_doc",
        "_id" : "wgugp4MBfEI_j4sNSA00",
        "_score" : null,
        "_source" : {
          "lead_id" : "lead_2",
          "value" : 9
        },
        "fields" : {
          "lead_id.keyword" : [
            "lead_2"
          ]
        },
        "sort" : [
          9
        ],
        "inner_hits" : {
          "most_recent" : {
            "hits" : {
              "total" : {
                "value" : 2,
                "relation" : "eq"
              },
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "index11",
                  "_type" : "_doc",
                  "_id" : "wQugp4MBfEI_j4sNQg3t",
                  "_score" : null,
                  "_source" : {
                    "lead_id" : "lead_2",
                    "value" : 12
                  },
                  "sort" : [
                    12
                  ]
                }
              ]
            }
          }
        }
      },
      {
        "_index" : "index11",
        "_type" : "_doc",
        "_id" : "vwugp4MBfEI_j4sNNw0q",
        "_score" : null,
        "_source" : {
          "lead_id" : "lead_1",
          "value" : 22
        },
        "fields" : {
          "lead_id.keyword" : [
            "lead_1"
          ]
        },
        "sort" : [
          22
        ],
        "inner_hits" : {
          "most_recent" : {
            "hits" : {
              "total" : {
                "value" : 3,
                "relation" : "eq"
              },
              "max_score" : null,
              "hits" : [
                {
                  "_index" : "index11",
                  "_type" : "_doc",
                  "_id" : "wAugp4MBfEI_j4sNPQ3F",
                  "_score" : null,
                  "_source" : {
                    "lead_id" : "lead_1",
                    "value" : 67
                  },
                  "sort" : [
                    67
                  ]
                }
              ]
            }
          }
        }
      }
    ]

Parent document will still have values sorted as per general sort. Group sort values can be picked from inner hits