How to group sum aggregations by some field?

OpenSearch
1

Hello there! I’m trying but I can’t figure out how to group sum aggregations by some field value. I have documents that look like this:

{
    "_index": "opportunity",
    "_id": "3575511",
    "_score": 4.4735565,
    "_source": {
        "id": 3575511,
        "campaign_id": 260,
        "campaign_name": "Name",
        "campaign_message": "Campaign message",
        "campaign_end_date": "0001-01-01T00:00:00Z",
        "event_campaign": {
            "conversion": false,
            "type": false
        },
        "stage_id": 4,
        "stage": {
            "id": 4,
            "name": "Stage Name",
            "description": "Stage Description",
            "slug": "stage-slug"
        },
        "status_id": 6,
        "status": {
            "id": 6,
            "name": "Status Name",
            "description": "Status Description",
            "slug": "status-slug",
            "status_type": 0
        },
        "origin_id": 0,
        "attendant_id": 543,
        "title": "Opportunity Title",
        "value": 0,
        "subtotal": 0,
        "comment": "",
        "client": {
            "id": 2485260,
            "registered": true,
            "id_fc": 15982726,
            "email": "client@email.com",
            "name": "Client Name",
            "document": "01020300506070809",
            "representative": "",
            "phones": [
                {
                    "id": 5483488,
                    "type": true,
                    "ddi": 55,
                    "number": "000102030405",
                    "created_at": "2023-10-04T23:48:42Z",
                    "updated_at": "2024-09-26T14:23:28Z"
                }
            ],
            "created_at": "2023-10-04T23:48:42Z",
            "updated_at": "2024-09-26T14:23:28Z"
        },
        "created_by": "",
        "created_at": "2024-09-26T14:23:28Z",
        "updated_at": "2024-09-26T14:23:28Z",
        "lifetime": "Since 4 months and 4 days",
        "tasks_total": 0,
        "tasks_open": 0,
        "date_next_task": "",
        "favorite": false,
        "finished": false,
        "reason_loss_id": 0,
        "reason_loss_name": "",
        "sales_opportunity": 0,
        "attendant": {
            "id": 543,
            "email": "attendant@email.com",
            "name": "Attendant Name"
        },
        "order": null,
        "order_db": null,
        "tasks": null,
        "budget": null,
        "products": [
            {
                "id": 8924399,
                "opportunity_id": 3575069,
                "sku": "652991",
                "name": "Product #1",
                "brand": "Brand #1",
                "seller": {
                    "id": 0,
                    "name": "Seller #1"
                },
                "type": "1P",
                "cabecasa": true,
                "image": "img.url",
                "quantity": 1,
                "unit_price": 3841.51,
                "total_price": 3841.51,
                "created_at": "2024-07-26T17:29:25Z",
                "updated_at": "2024-07-26T17:29:25Z"
            }
        ],
        "type_change": null,
        "type_opportunity": false,
        "conversion_opportunity": false,
        "bond_exchange": null,
        "coupon": {
            "id": 0,
            "opportunity_id": 0,
            "promo_id": 0,
            "code": "",
            "active": false,
            "updated_at": "0001-01-01T00:00:00Z"
        }
    }
}

I want to sum the value field, grouped by the stage.slug value, in hopes (and dreams) that I’ll have the total value of all documents that match the stage.slug value.

I haven’t tried much (as I was also trying to count the documents that match the conditions, but I have to focus one thing at a time), but my last attempt I was using this:

{
    "query": {
        "nested": {
            "path": "stage",
            "query": {
                "match": {
                    "stage.slug": "atendente"
                }
            }
        }
    },
    "aggs": {
        "value_count": {
            "sum": {
                "field": "value"
            }
        }
    },
    "size": 0,
    "sort": [
        {
            "created_at": {
                "order": "desc"
            }
        }
    ]
}

Is this kind of grouping + sum + count scenario possible?

2

Well, it took a while but thanks to one of those shiny AI models out there, I was able to figure out what I needed. This is my current request:

{
    "aggs": {   
        "agg_name": {
            "nested": {
                "path": "pathToNested"
            },
            "aggs": {
                "agg_name: {
                    "terms": {
                        "field": "pathToNested.Field",
                        "size": 10
                    },
                    "aggs": {   
                        "reverse_agg_name": { 
                            "reverse_nested": {},
                            "aggs": {
                                "agg_name": {
                                    "sum": {
                                        "field": "document.Field"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

So, here’s how it works: in my case, I needed to group documents by the value of one of its nested objects. With this top-level aggregation, it works as expected - all documents that match the query I’m not showing here are grouped by one of the nested fields’ value.

Then, I have another aggregation inside: this time, it is a Reverse nested aggregation, whith means that the aggregation will integrate values from the parent (in this case, the main document), which contains the field I’ll use. Then, I defined a sum aggregation on one document field, which in my case contains numbers. This way, all documents matching the previous imaginary query will have that field added to the total.

This particular scenario can be used to return sales grouped by the person that did the sale (nested object) and the values from all sales (document field).

1 Like