# How to filter buckets based on the comparison of two sub-aggregation metrics in ElasticSearch (python)?

**URL:** <https://discuss.elastic.co/t/how-to-filter-buckets-based-on-the-comparison-of-two-sub-aggregation-metrics-in-elasticsearch-python/327498>\
**Category:** Elasticsearch\
**Created:** [March 12, 2023, 8:59am UTC](https://discuss.elastic.co/t/how-to-filter-buckets-based-on-the-comparison-of-two-sub-aggregation-metrics-in-elasticsearch-python/327498 "2023-03-12T08:59:20Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ashar\_Ahmad](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ashar_ahmad/32/118288_2.png) [@Ashar\_Ahmad](https://discuss.elastic.co/u/Ashar_Ahmad)\
**Post date:** [March 12, 2023, 8:59am UTC](https://discuss.elastic.co/t/how-to-filter-buckets-based-on-the-comparison-of-two-sub-aggregation-metrics-in-elasticsearch-python/327498/1 "2023-03-12T08:59:20Z")

</div>

My index has documents with the following fields: user\_id, user\_name, post\_text, post\_sentiment where post\_sentiment is of type double, and represents the sentiment of the post. A post\_sentiment greater than 0 indicates it is a happy post, while a post\_sentiment lesser than 0 indicates a sad post.

I am trying to retrieve the users who have more happy posts than sad posts. I am using the Elasticsearch high-level python library.

I have created the following function, which seems correct to me logically. However, running it yields Error message: TransportError(500, 'search\_phase\_execution\_exception'). I have made sure the problem is not with the connection or the index, but in fact with the query structure. Please indicate what I might be doing wrong here.

```auto
def users_more_sentiment_posts(search_object: Search):
    a = search_object.aggs.bucket(
            "users",
            "terms",
            field="user_id"
        ).metric(
            "positive_post_count_per_bucket",
            "range", 
            field="post_sentiment", 
            ranges= [{'from': 0.0}]
        ).metric(
            "negative_post_count_per_bucket",
            "range", 
            field="post_sentiment", 
            ranges= [{'to': 0.0}]
        ).pipeline(
            "happy_posts",
            "bucket_selector",
            buckets_path={
                "positiveCount": "positive_post_count_per_bucket._count",
                "negativeCount": "negative_post_count_per_bucket._count"
            },
            script="params.positiveCount > params.negativeCount"
        ).bucket(
            "posts",
            "top_hits",
            size=10
        )

    response = search_object.execute()

```

---

<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:** [April 9, 2023, 8:59am UTC](https://discuss.elastic.co/t/how-to-filter-buckets-based-on-the-comparison-of-two-sub-aggregation-metrics-in-elasticsearch-python/327498/2 "2023-04-09T08:59:39Z")

</div>

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