Filter elasticsearch bucket aggregation based on term field

Question

I have a list of products (deal entities) and I'm attempting to create a bucket aggregation by categories, ordered by the sum of available_stock.

This all works fine, but I want to exclude such categories from the resulting aggregation that don't have level set to 1 (In other words, I only want to keep aggregations on category where level IS 1).

I am aware that elasticsearch provides "exclude" and "include" parameters, but these only work on the same field I'm aggregating on (deal.category.id in this case)

This is my sample deal document:

{
  "_source": {
    "id": 392745,
    "category": [
      {
        "id": 17575,
        "level": 2
      },
      {
        "id": 17574,
        "level": 1
      },
      {
        "id": 17572,
        "level": 0
      }
    ],
    "stats": {
      "available_stock": 500
    }
  }
}

And this would be the query:

{
  "query": {
    "filtered": {
      "query": {
        "match_all": {}
      },
    }
  },
  "aggs": {
    "mainAggregation": {
      "terms": {
        "field": "deal.category.id",
        "order": {
          "available_stock": "desc"
        },
        "size": 3
      },
      "aggs": {
        "available_stock": {
          "sum": {
            "field": "deal.stats.available_stock"
          }
        }
      }
    }
  },
  "size": 0
}

And my resulting aggregation, sadly including category 17572 with level 0.

{
  "aggregations": {
    "mainAggregation": {
      "buckets": [
        {
          "key": 17572,
          "doc_count": 30,
          "available_stock": {
            "value": 24000
          }
        },
        {
          "key": 17598,
          "doc_count": 10,
          "available_stock": {
            "value": 12000
          }
        },
        {
          "key": 17602,
          "doc_count": 8,
          "available_stock": {
            "value": 6000
          }
        }
      ]
    }
  }
}

P.S.: Currently on ElasticSearch 1.6

Update 1: Still stuck on the problem after various experiments with various combimation of subaggregations.

CrazyMuffin CrazyMuffin · Accepted Answer · 2018-12-11T13:29:37

0

votes

I have found this impossible to solve and decided to go with two separate queries.

Filter elasticsearch bucket aggregation based on term field

1 Answers