Aggregate by multiple fields of object in array

My data looks like this -

[
  {
    "orderId": "1",
    "items": [
      {"id": "1", "name": "Item1"},
      {"id": "2", "name": "Item2"}
    ]
  },
  {
    "orderId": "2",
    "items": [
      {"id": "1", "name": "Item1"}
    ]
  }
]

I want to aggregate by item id and name.

I am expecting a result like this:

{"id": "1", "name": "Item1"} -> 2
{"id": "2", "name": "Item2"} -> 1

Hi @Kaustav_Shouvik

You did not provide your mapping but assuming that your items field is of type nested you can use nested aggs and multi-terms aggs.

I recommend reading this article.

An example would be this:

{
  "size": 0,
  "aggs": {
    "agg_item": {
      "nested": {
        "path": "items"
      },
      "aggs": {
        "id_and_name": {
          "multi_terms": {
            "terms": [
              {
                "field": "items.id.keyword"
              },
              {
                "field": "items.name.keyword"
              }
            ]
          }
        }
      }
    }
  }
}
1 Like

It returns results also for (id - 1, name - item2), which is not what I want.

I only want two fields:

  1. (id - 1, name - item1)
  2. (id - 2, name - item2)

Are the results incorrect or is the format not what you want? Share your query.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.