Sum of a field in a parent node and grouping by a field in a child node

Elastic Stack Elasticsearch
1

I'm trying to get the sum of a field in a parent node while aggregating on a field of a child node. For example, this is what I've setup:

PUT order

POST order/_mapping
{
  "properties": {
    "order_items": {
      "type": "nested",
      "properties": {
        "product_id": {
          "type": "long"
        },
        "product": {
          "type": "nested",
          "properties": {
            "manufacturer" : {
              "type": "nested",
              "properties": {
                "name": {
                   "type": "keyword"
                }
              }
            },
            "name": {
              "type": "keyword"
            },
            "price": {
              "type": "long"
            }
          }
        }
      }
    }
  }
}

POST order/_bulk
{"index":{}}
{"order_items":[{"product":{"name":"book","price":10, "manufacturer": {"name": "alpha"}}},{"product":{"name":"pencil","price":1, "manufacturer": {"name": "alpha"}}}]}
{"index":{}}
{"order_items":[{"product":{"name":"pen","price":5, "manufacturer": {"name": "beta"}}},{"product":{"name":"eraser","price":2, "manufacturer": {"name": "alpha"}}}]}

I want to sum all the product prices and group them by the manufacturer name. So the final result should be something like:

Manufacturer: Alpha
Sum Price: 13  (because 10 + 1 + 2)

Manufacturer: Beta
Sum Price: 5 (because only one instance with 5)

I tried this query:

GET order/_search
{
  "size": 0,
  "aggs": {
    "manufacturerpath": {
      "nested": {
        "path": "order_items.product.manufacturer"
      },
      "aggs": {
        "manufacturer": {
          "terms": {
            "field": "order_items.product.manufacturer.name"
          },
          "aggs": {
            "productpath": {
              "nested": {
                "path": "order_items.product"
              },
              "aggs": {
                "sum_price": {
                  "sum": {
                    "field": "order_items.product.price"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

But it gave the result:

{
"aggregations": {
    "manufacturerpath": {
      "doc_count": 4,
      "manufacturer": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "alpha",
            "doc_count": 3,
            "productpath": {
              "doc_count": 2,
              "sum_price": {
                "value": 15
              }
            }
          },
          {
            "key": "beta",
            "doc_count": 1,
            "productpath": {
              "doc_count": 1,
              "sum_price": {
                "value": 1
              }
            }
          }
        ]
      }
    }
  }
}

Meaning alpha has 15 and beta has 1. Can someone tell me what I did wrong?

2

Hi @learningelastic

Looking at the indexed documents, does it make sense for Product and Manufacturer to be "nested"?
If you make them objects you will get the desired answer.

3

I'm not sure if I needed to apply the type: nested to the product or manufacturer. I tried quite a few permutations and I'm getting lost because I don't yet understand the fundamentals of summing on a field in a parent node when i want to group by a field of a child element. And assuming the parent node is within an array.

4

I did some changes. Look:

PUT order

POST order/_mapping
{
  "properties": {
    "order_items": {
      "type": "nested",
      "properties": {
        "product_id": {
          "type": "long"
        },
        "product": {
          "properties": {
            "manufacturer": {
              "properties": {
                "name": {
                  "type": "keyword"
                }
              }
            },
            "name": {
              "type": "keyword"
            },
            "price": {
              "type": "long"
            }
          }
        }
      }
    }
  }
}

Documents

POST order/_doc
{
  "order_items": [
    {
      "product": {
        "name": "pen",
        "price": 5,
        "manufacturer": {
          "name": "beta"
        }
      }
    },
    {
      "product": {
        "name": "eraser",
        "price": 2,
        "manufacturer": {
          "name": "alpha"
        }
      }
    }
  ]
}

POST order/_doc
{
  "order_items": [
    {
      "product": {
        "name": "book",
        "price": 10,
        "manufacturer": {
          "name": "alpha"
        }
      }
    },
    {
      "product": {
        "name": "pencil",
        "price": 1,
        "manufacturer": {
          "name": "alpha"
        }
      }
    }
  ]
}

Query

GET order/_search
{
  "size": 0,
  "aggs": {
    "manufacturerpath": {
      "nested": {
        "path": "order_items"
      },
      "aggs": {
        "manufacturer": {
          "terms": {
            "field": "order_items.product.manufacturer.name"
          },
          "aggs": {
            "sum_price": {
              "sum": {
                "field": "order_items.product.price"
              }
            }
          }
        }
      }
    }
  }
}

Output:

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 2,
      "relation": "eq"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "manufacturerpath": {
      "doc_count": 4,
      "manufacturer": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "alpha",
            "doc_count": 3,
            "sum_price": {
              "value": 13
            }
          },
          {
            "key": "beta",
            "doc_count": 1,
            "sum_price": {
              "value": 5
            }
          }
        ]
      }
    }
  }
}
1 Like
5

Your answer worked! And I have a related question on this page here in case you know the answer. Thank you in advance!

1 Like
6

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