How to query with GROUP BY and COUNT(*)

[Remon_Andrew]

Hi all,

I have this query that I used to run on MySQL:
SELECT from_mobile_number, event_name, COUNT(*) AS count FROM analytics_events WHERE date >= '2022-10-01 00:00:00' AND date <= '2022-09-30 23:59:59' AND from_mobile_number != 'N/A' GROUP BY from_mobile_number, event_name;

Now the table is moved to Elasticsearch, and I need an equivalent way to run it
I have this query, but I still don’t know how to return the COUNT(*) AS count using it:

{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "bool": {
                        "must_not": [
                            {
                                "term": {
                                    "from_mobile_number.keyword": {
                                        "value": "N/A"
                                    }
                                }
                            }
                        ],
                        "boost": 1.0
                    }
                },
                {
                    "range": {
                        "date": {
                            "gte": "2022-10-01T00:00:00.000+04:00",
                            "lte": "2022-10-31T23:59:59.000+04:00",
                            "time_zone": "Z",
                            "boost": 1.0
                        }
                    }
                }
            ],
            "boost": 1.0
        }
    },
    "_source": ["from_mobile_number", "event_name"],
    "aggs": {
        "agg1": {
            "terms": {
                "field": "from_mobile_number.keyword"
            },
            "aggs": {
                "agg2": {
                    "terms": {
                        "field": "event_name.keyword"
                    }
                }
            }
        }
    }
}

I need your comments on it—is it the correct way? and how can I return the "COUNT(*) AS count"

Thanks in advance