Group field by name and date

[Jan_O]

Hi,
I have been looking for a way to group information by name and date.
We have a setup with thousands of tests running on different products, what I want to do is fetch all failing test cases but only count them once per date. So if a test has failed on 5 different products it should only be registered as one fail that specific day.

I guess this would be the corresponding sql queery if it was a mysql database:

SELECT  test_name, count(DISTINCT DATE(timestamp))
  FROM mysql_test
  GROUP by test_name;

Thanks and all the best

[Andrei_Stefan]

Hi @Jan_O,

Slightly unrelated, but you can use the translate API of Elasticsearch SQL to help you create a query DSL from that sql query. To give you an example, something like this should help you:

{
    "size": 0,
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "groupby": {
            "composite": {
                "size": 1000,
                "sources": [
                    {
                        "13": {
                            "terms": {
                                "field": "test_name",
                                "missing_bucket": true,
                                "order": "asc"
                            }
                        }
                    }
                ]
            },
            "aggregations": {
                "22": {
                    "cardinality": {
                        "field": "timestamp"
                    }
                }
            }
        }
    }
}

If timestamp is in millis, though, this won't help you.
If it is in millis, you need to add scripting to your cardinality aggregation above and convert your timestamps in dates (no time component). How to define scripts is here in the documentation.

Hope this helps.