Elasticsearch taking first of items by grouping

Question

I'm having an issue finding the best approach to the following elastic search. If I have data as below:-

{name: "A", size: "10", "Variant": "Large"},
{name: "B", size: "8", "Variant": "Medium"},
{name: "C", size: "6", "Variant": "Small"},
{name: "D", size: "8", "Variant": "Large"},
{name: "C", size: "4", "Variant": "Small"}

I'd like to be able to return the results:-

{name: "A", size: "10", "Variant": "Large"},
{name: "B", size: "8", "Variant": "Medium"},
{name: "C", size: "6", "Variant": "Small"}

i.e. take the first (TOP 1) based on the variant field. I think this is equivalent to doing a CTE and ROW_NUMBER in SQL.

Thank :)

score 1 · Answer 1 · answered Jul 05 '16 at 09:40

Something like this probably:

{
  "size": 0,
  "aggs": {
    "variants": {
      "terms": {
        "field": "Variant",
        "order": {
          "max_size": "desc"
        }
      },
      "aggs": {
        "top1": {
          "top_hits": {
            "size": 1,
            "sort": [{"size":{"order": "desc"}}]
          }
        },
        "max_size": {
          "max": {
            "field": "size"
          }
        }
      }
    }
  }
}

Elasticsearch taking first of items by grouping

1 Answers1