# Aggregate data per document

**URL:** <https://discuss.elastic.co/t/aggregate-data-per-document/334812>\
**Category:** Elasticsearch\
**Created:** [May 31, 2023, 1:50pm UTC](https://discuss.elastic.co/t/aggregate-data-per-document/334812 "2023-05-31T13:50:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![JohnJoe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnjoe/32/121671_2.png) [@JohnJoe](https://discuss.elastic.co/u/JohnJoe)\
**Post date:** [May 31, 2023, 1:50pm UTC](https://discuss.elastic.co/t/aggregate-data-per-document/334812/1 "2023-05-31T13:50:43Z")

</div>

Hi All,

I am wondering if the following is possible. I want to be able aggregate nested data within a document and then filter by the aggregated data.

So if we have

```auto
PUT warehouse/
{
  "mappings": {
  "properties": {
    "inventory": {
      "type": "nested",
      "properties": {
        "equipment": {
          "type": "keyword"
        },
        "price": {
          "type": "float"
        },
        "shopId": {
          "type": "keyword"
        }
      }
    },
    "profile": {
      "properties": {
        "name": {
          "type": "keyword"
        }
      }
    }
  }
  }
}

```

and then put data

```auto
PUT warehouse/_doc/1
{
  "profile": {
    "name": "Place1"
  },
  "inventory": [
    {"equipment":"guitar", "price": 1000.00, "shopId":"1"},
    {"equipment":"guitar", "price": 200.00, "shopId":"2"},
    {"equipment":"guitar", "price": 1.0, "shopId":"4"}
  ]
}

```

etc

I need to do filter by shopIds, say shopId 1 and shopId 2. Then aggregate that data per document, so for document above the average guitar price for shopId1 and shopId2 is 150.

I then want to only return documents and values that meed a criteria, so I say shopId1 + shopId2 AND average price \>130.

I am able to get some aggregation working but it is aggregation across all the documents returned, not per document.

Hoping someone can help me!

JohnJoe

---

<div class="post-metadata">

**Author:** ![JohnJoe](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnjoe/32/121671_2.png) [@JohnJoe](https://discuss.elastic.co/u/JohnJoe)\
**Post date:** [May 31, 2023, 2:10pm UTC](https://discuss.elastic.co/t/aggregate-data-per-document/334812/2 "2023-05-31T14:10:38Z")

</div>

The following gives me the average price across **all** documents that match the search

```auto
GET warehouse/_search
{
  "query": {
    "nested": {
      "path": "inventory",
      "query": {
        "bool": {
              "should": [
                {
                  "term": {
                    "inventory.shopId": "1"
                  }
                },
                {
                  "term": {
                    "inventory.shopId": "2"
                  }
                }
              ]
            }
      }
    }
  },
  "aggs": {
    "inventory": {
      "nested": {
        "path": "inventory"
      },
      "aggs": {
        "priceAgg": {
          "filter": {
            "bool": {
              "should": [
                {
                  "term": {
                    "inventory.shopId": "1"
                  }
                },
                {
                  "term": {
                    "inventory.shopId": "2"
                  }
                }
              ]
            }
          },
          "aggs": {
            "avg_price": {
              "avg": {
                "field": "inventory.price"
              }
            }
          }
        }
      }
    }
  }
}

```

Result:

```auto
"aggregations" : {
    "inventory" : {
      "doc_count" : 9,
      "priceAgg" : {
        "doc_count" : 3,
        "avg_price" : {
          "value" : 2000.0
        }
      }
    }

```

But what I need is the price for the criteria per document

---

<div class="post-metadata">

**Author:** ![eMitch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emitch/32/93607_2.png) [@eMitch](https://discuss.elastic.co/u/eMitch)\
**Post date:** [June 13, 2023, 7:18pm UTC](https://discuss.elastic.co/t/aggregate-data-per-document/334812/3 "2023-06-13T19:18:31Z")

</div>

Hey @JohnJoe and welcome to the community!

In a nested structure, each child entry is its own document. In this way, it makes doing nested aggregations a little interesting.

I worry about the use of nested in this fashion if these lists are going to grow/change. Have you considered changing the index mapping to be "item" centric instead of the current "profile" centric?

This would give you an individual document for each inventory item and significantly more flexible queries for what you're looking to accomplish.

While it may seem more voluminous, under the hood, the number of documents will be similar, yet easier to manage and it will scale better as you grow the index.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 11, 2023, 7:18pm UTC](https://discuss.elastic.co/t/aggregate-data-per-document/334812/4 "2023-07-11T19:18:50Z")

</div>

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