How to get average of items in an array of a nested field?

Elastic Stack Elasticsearch
1

I'm trying to get the average price of products that are in stock across my different physical warehouses. I made this index:

PUT warehouse
POST warehouse/_mapping
{
"properties": {
      "inventory": {
        "properties": {
          "equipment": {
            "type": "keyword"
          },
          "price": {
            "type": "float"
          }
        }
      },
      "profile": {
        "properties": {
          "name": {
            "type": "keyword"
          }
        }
      }
    }
}

Then I inserted 3 documents each with the name of a warehouse and some inventory

POST warehouse/_doc
{
  "profile": {
    "name": "Texas"
  },
  "inventory": [
    {"equipment":"guitar", "price": 20.00},
    {"equipment":"book", "price": 25.00}
  ]
}

POST warehouse/_doc
{
  "profile": {
    "name": "Ohio"
  },
  "inventory": [
    {"equipment":"piano", "price": 20.00},
    {"equipment":"book", "price": 25.00}
  ]
}

POST warehouse/_doc
{
  "profile": {
    "name": "New Mexico"
  },
  "inventory": [
    {"equipment":"guitar", "price": 20.00},
    {"equipment":"book", "price": 25.00},
    {"equipment":"laptop", "price": 250.00}
  ]
}

Then I ran this query to get the average price of each equipment I have:

GET warehouse/_search
{
  "aggs": {
    "0": {
      "terms": {
        "field": "inventory.equipment"
      },
      "aggs": {
        "1": {
          "avg": {
            "field": "inventory.price"
          }
        }
      }
    }
 }
}

And this was the aggregation response:

  "aggregations": {
    "0": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "1": {
            "value": 55
          },
          "key": "book",
          "doc_count": 3
        },
        {
          "1": {
            "value": 68
          },
          "key": "guitar",
          "doc_count": 2
        },
        {
          "1": {
            "value": 98.33333333333333
          },
          "key": "laptop",
          "doc_count": 1
        },
        {
          "1": {
            "value": 22.5
          },
          "key": "piano",
          "doc_count": 1
        }
      ]
    }
  }
}

These results don't look correct because I was expecting piano to be 20, guitar to be 20, book to be 25, and laptop to be 250.

What did I do wrong?

2

Have a look at nested type. Here your array is flattened by default.

4

Thanks for suggestion! My question is based on the concept of nested field type that I read on this page:

I also tried adding the property properties.inventory.type: "nested" to my mappings so that it looks like this:

POST warehouse/_mapping
{
  "properties": {
    "inventory": {
      "type": "nested",
      "properties": {
        "equipment": {
          "type": "keyword"
        },
        "price": {
          "type": "float"
        }
      }
    },
    "profile": {
      "properties": {
        "name": {
          "type": "keyword"
        }
      }
    }
  }
}

But now my search query returns nothing in the aggregations as shown here:

{
  "aggregations": {
    "0": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": []
    }
  }
}

Any ideas what I'm missing next?

5

Maybe I figured it out. I also changed my search query by wrapping it with another level of aggregation to mention a nested path like this:

GET warehouse/_search
{
  "aggs": {
    "inventory": {
      "nested": {
        "path": "inventory"
      },
      "aggs": {
        "0": {
          "terms": {
            "field": "inventory.equipment"
          },
          "aggs": {
            "1": {
              "avg": {
                "field": "inventory.price"
              }
            }
          }
        }
      }
    }
  }
}

This seems to give the correct results so far, but need more testing to confirm

{
"aggregations": {
    "inventory": {
      "0": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "1": {
              "value": 25
            },
            "key": "book",
            "doc_count": 3
          },
          {
            "1": {
              "value": 20
            },
            "key": "guitar",
            "doc_count": 2
          },
          {
            "1": {
              "value": 250
            },
            "key": "laptop",
            "doc_count": 1
          },
          {
            "1": {
              "value": 20
            },
            "key": "piano",
            "doc_count": 1
          }
        ]
      },
      "doc_count": 7
    }
  }
}
6

And a related problem...by adding properties.inventory.type: "nested" to the mapping, kibana lens can no longer access any of the inventory.* fields. I posted the problem as a separate question here:

7

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