Aggregate by multiple fields of object in array

[Kaustav_Shouvik]

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

[RabBit_BR (andre.coelho)]

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"
              }
            ]
          }
        }
      }
    }
  }
}

[Kaustav_Shouvik]

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)

[RabBit_BR (andre.coelho)]

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