elasticsearch sorting in aggregation not working

Question

I am using aggregation to group a field based on the sum of other field.

It's working but the output is not similar to the one I am getting from database query.

ES query:

{
    "from": 0,
    "size": 0
,
    "aggs": {"domain_agg": {
            "terms": {
                "field": "domain_name.keyword"
            },
            "aggs": {
                "domain_store_count_sum": {
                    "sum": {
                        "field": "store_count"
                    }
                },
                "domain_store_count_agg": {
                    "bucket_sort": {
                        "sort": [
                            {
                                "domain_store_count_sum": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "size": 5
                    }
                }
            }
        }

    },
    "query": {
        "query_string": {
            "query": "(*:*) "
        }
    }
}

output:

"domain_agg": { "doc_count_error_upper_bound": 9, "sum_other_doc_count": 1442, "buckets": [ { "key": "advanceautoparts.com", "doc_count": 2, "domain_store_count_sum": { "value": 11258 } }, { "key": "tesla.com", "doc_count": 3, "domain_store_count_sum": { "value": 6795 } }, { "key": "aldi.us", "doc_count": 2, "domain_store_count_sum": { "value": 3678 } }, { "key": "alamo.com", "doc_count": 2, "domain_store_count_sum": { "value": 566 } }, { "key": "ajg.com", "doc_count": 2, "domain_store_count_sum": { "value": 440 } } ] }

But when I ran the same in database I got differrent output.

DB query:

SELECT domain_name, SUM(store_count) AS store_count
FROM table
GROUP BY domain_name order by store_count desc

Output:

'subway.com','57384'
'bmoharris.com','56876'
'peets.com','37472'
'citibank.com','32348'
'shell.us','23016'

Why I am getting different result in elasticsearch, while both have the same data.

Answer 1

You don't have to precise the mapping in your main agg:

"field": "domain_name.keyword" => "field": "domain_name"

and you should remove the query part.

"query": {
        "query_string": {
            "query": "(*:*) "
        }
    }